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