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

For some reason when I pressed Enter after the 2nd row it posted my reply. The first value '23456' is returned for Lead, when it should be '12345'. The same is true for the Lag. It also returns the current record/row. I've rewritten the code multiple ways, used every possible combination of ORDER BY , and PARTITION. I can cut and paste examples from several sites and they work, but the data is simple numerical examples with no '=' to WHERE clause. I think the 'equal to' is preventing the next or previous record from being displayed. — polczym, Mar 18 '16 at 16:49
@polczym: Update sample data in your question. Not here in comment. — Krishnraj Rana, Mar 18 '16 at 16:49

score 2 · Accepted Answer · answered Mar 18 '16 at 21:43

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

You are 100% correct. There is more data, and your assumptions are spot on. I was not returning the full set. Good job - and thank you for analyzing this and solving my problem. — polczym, Mar 19 '16 at 12:34

LEAD/LAG Not Incrementing/Decrementing

1 Answers1