Get the Next and Previous Row Value in SQL Server
This post can help you to get the next and previous row Value in SQL Server using T-SQL. We had a requirement to get the previous row information and compare it with the current row value. Usually we do not suggest performing these operations at database side rather it can be done from application side as it easier the process in navigating row values. But in some cases we have to do it from back-end. Here is an example to get the next and previous row value in SQL Server using T-SQL script.
/**************************************************************/ /*** To Find the Next and Previous Row Value in SQL Server ***/ /*** Works On: SQL Server 2012, 2014, 2016 ********************/ /**************************************************************/ -- Create a Dummy Table and Insert data CREATE TABLE ScoreCard( ID INT IDENTITY, Name VARCHAR(50) NOT NULL, Score INT NOT NULL, ScoredOn SMALLDATETIME NOT NULL); GO INSERT INTO ScoreCard (Name,Score,ScoredOn) VALUES ('A',80,'2017-01-20'), ('A',50,'2017-01-22'), ('A',65,'2017-01-23'), ('A',92,'2017-01-25'), ('A',100,'2017-01-30'); -- Report Requirement -- Below is the Expected output -- Name, ScoredOn, PreviousScore, CurrentScore, NextScore /*************************/ /*** 2012, 2014, 2016 ****/ /*************************/ SELECT Name, CONVERT(VARCHAR(10),ScoredOn,103) AS 'ScoredOn', LAG(s.Score) OVER (ORDER BY s.ID) AS 'PreviousScore', Score AS 'CurrentScore', LEAD(s.Score) OVER (ORDER BY s.ID) AS 'NextScore' FROM dbo.ScoreCard s -- When LEAD and LAG is not available i.e Before 2012 /*************************/ /*** Before 2012 *********/ /*************************/ ;WITH SCCTE AS ( SELECT s.Name, CONVERT(VARCHAR(10),s.ScoredOn,103) AS 'ScoredOn', s.Score, ROW_NUMBER() OVER (ORDER BY s.ID) AS rownum FROM dbo.ScoreCard s ) SELECT SCCTE.Name, SCCTE.ScoredOn, p.Score AS 'PreviousScore', SCCTE.Score AS 'CurrentScore', n.Score AS 'NextScore' FROM SCCTE LEFT JOIN SCCTE p ON p.rownum = SCCTE.rownum - 1 -- To get Previous row info LEFT JOIN SCCTE n ON n.rownum = SCCTE.rownum + 1 -- To Get next row info GO -- Drop the dummy table DROP TABLE ScoreCard;
Download the script file: Get the Next and Previous Row Value in SQL Server
Very Good logic Uday.
Keep it up.
Thanks sir 🙂
Happy Reading
SQL The One Team
http://www.udayarumilli.com