Find value before and after a value in a column

Question

I'm using the lag() function to find the value that occurred previously in the same field for any particular value.

Is there a function to find the one next/after the field value?

example data:

id     desc
1       line1
1       line2
1       line 3
2       line 1
2       line 2
2       line 3

expected result:

current   lg_desc      next
line 2    line1       line 3
line 2    line1       line 3

the query i have so far is:

select  Desc as current,
              lag(Description)  
                over (partition by ID order by TimestampUTC) as lg_desc
        from  #temp1

please show `TimestampUTC` in your sample data – Squirrel Jan 13 '21 at 06:49 — Squirrel, Jan 13 '21 at 06:49

score 1 · Accepted Answer · answered Jan 13 '21 at 06:48

Use lead function like this

select  Desc as current,
              lag(Description)  
                over (partition by ID order by TimestampUTC) as lg_desc,
              lead(Description)  
                over (partition by ID order by TimestampUTC) as ld_desc
        from  #temp1

Find value before and after a value in a column

1 Answers1