Get Prev & Next Row Values in SQL Server with LEAD & LAG Clause

In this tutorial, i want to share how to get prev or next row value with LAG & LEAD clause in SQL Server. I used SQL Server 2014 to run above clause.

Here’s an example:

SELECT 
 LAG(SEQUENCE) OVER (ORDER BY SEQUENCE) PREV_VALUE,
 LEAD(SEQUENCE) OVER (ORDER BY SEQUENCE) NEXT_VALUE,
 *
FROM
(
 SELECT 1 SEQUENCE, 'VALUE_1' COL1 UNION ALL
 SELECT 2 SEQUENCE, 'VALUE_2' COL1 UNION ALL
 SELECT 3 SEQUENCE, 'VALUE_3' COL1 UNION ALL
 SELECT 4 SEQUENCE, 'VALUE_4' COL1 UNION ALL
 SELECT 5 SEQUENCE, 'VALUE_5' COL1 UNION ALL
 SELECT 6 SEQUENCE, 'VALUE_6' COL1 UNION ALL
 SELECT 7 SEQUENCE, 'VALUE_7' COL1 UNION ALL
 SELECT 8 SEQUENCE, 'VALUE_8' COL1 UNION ALL
 SELECT 9 SEQUENCE, 'VALUE_9' COL1 UNION ALL
 SELECT 10 SEQUENCE, 'VALUE_10' COL1 
) A
ORDER BY SEQUENCE

CMIIW :).

Leave a Reply

Your email address will not be published. Required fields are marked *

Afiseaza emoticoanele Locco.Ro