LAG and LEAD will retrieve observations prior and after. Let’s say we have…
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE #temp ( number int, alphabet VARCHAR(10) ) INSERT INTO #temp VALUES (1,'a'),(2,'b'),(4,'c'),(3,'d'),(5,'e') |
|
1 |
SELECT * FROM #temp |
|
1 2 3 4 5 6 7 8 9 |
number alphabet ----------- ---------- 1 a 2 b 4 c 3 d 5 e (5 row(s) affected) |
Let’s retrieve prior and after alphabets.
|
1 2 3 4 |
SELECT alphabet, number, LAG(alphabet) OVER (ORDER BY alphabet) AS Previous_Alphabet, LEAD(alphabet) OVER (ORDER BY alphabet) AS Next_Alphabet FROM #temp |
|
1 2 3 4 5 6 7 8 9 |
alphabet number Previous_Alphabet Next_Alphabet ---------- ----------- ----------------- ------------- a 1 NULL b b 2 a c c 4 b d d 3 c e e 5 d NULL (5 row(s) affected) |
OVER conveniently gives the choice of what to base on. Suppose we want to based in on ‘number,’ we then will get
|
1 2 3 4 |
SELECT alphabet, LAG(alphabet) OVER (ORDER BY number) AS Previous_Alphabet, LEAD(alphabet) OVER (ORDER BY number) AS Next_Alphabet FROM #temp |
|
1 2 3 4 5 6 7 8 9 |
alphabet number Previous_Alphabet Next_Alphabet ---------- ----------- ----------------- ------------- a 1 NULL b b 2 a d d 3 b c c 4 d e e 5 c NULL (5 row(s) affected) |