LEAD/LAG Not Incrementing/Decrementing

Question

I cannot get the Lead/Lag functions to increment/decrement properly and return the correct values. No matter what I do, it still returns the current row. I need to display the previous p.field2 value and succeeding p.field2 value when p.field3 value = '223344'.

BEGIN TRY
    SELECT p.field1, 
           p.field2,
           LEAD( p.field2, 1, 'No Lead') OVER (ORDER BY p.field1) AS 'Lead',
           LAG(  p.field2, 1, 'No Lag' ) OVER (ORDER BY p.field1) AS 'Lag',
           p.field3,
           p.field4
    FROM fieldTable p
    WHERE p.field3 = '223344' 
    ORDER BY p.field1
END TRY
BEGIN CATCH
.....
END CATCH

Example Data in Database
1, 'Mr. Smith', '112233', 'Info1'
2, 'Mr. Jones', '223344', 'Info2'
3, 'Mr. Davis', '334455', 'Info3'

Data Being Returned for both Lag and Lead
2, 'Mr. Jones', 'Mr. Jones', 'Mr. Jones', '223344', 'Info2'

I need 'Mr. Smith' to be returned for Lag and 'Mr. Davis' returned for Lead

Answer 1

Your query does not output what you say it does. You must have more sample data in your table than you're showing us.

With this data in the table:

field1      field2      field3  field4
1           Mr. Smith   112233  Info1
2           Mr. Jones   223344  Info2
3           Mr. Davis   334455  Info3

This is the output of your query:

field1  field2      Lead    Lag         field3  field4
2       Mr. Jones   No Lead No Lag      223344  Info2

Which is as expected. You're limiting your set in your WHERE condition, only selecting 1 row from you base table. Hence, there are no LAG or LEAD rows, and both return your given default values.

In your actual sample table, I'm sure you have several rows with field3 = '223344', bu all with the same field2 value?

You need to use the window functions on the FULL set, and then restrict your query afterwards. This can be done with a subquery. In this case, I've used a common table expression, which I believe is easier to read and test.

WITH
    BaseQuery AS (
        SELECT p.field1, 
                p.field2,
                LEAD( p.field2, 1, 'No Lead') OVER (ORDER BY p.field1) AS 'Lead',
                LAG(  p.field2, 1, 'No Lag' ) OVER (ORDER BY p.field1) AS 'Lag',
                p.field3,
                p.field4
        FROM fieldTable p
    )

SELECT
    *
FROM
    BaseQuery
WHERE
    field3 = '223344'

This produces the desired output:

field1  field2      Lead        Lag         field3  field4
2       Mr. Jones   Mr. Davis   Mr. Smith   223344  Info2