SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement?

The first email I read this morning had only one statement in it, and it gave me an idea to write this blog post.

“How to access Previous Row Value and Next Row Value in SELECT statement?”

Very interesting question indeed. The matter of the fact, I have written about this in my earlier blog Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012. Let us quickly understand it in it with the help of script.

For example here is the column where we want to get access to the previous row and the next row in SELECT statement.

USE AdventureWorks2012
GO
SELECT p.FirstName
FROM Person.Person p
ORDER BY p.BusinessEntityID
GO

Now let us use the new Analytic Function from SQL Server 2012 to get previous and next row values.

SELECT
LAG(p.FirstName) OVER (ORDER BY p.BusinessEntityID) PreviousValue,
p.FirstName,
LEAD(p.FirstName) OVER (ORDER BY p.BusinessEntityID) NextValue
FROM Person.Person p
GO

You can see it is very simple to get Previous and Next value with the help of Lead and Lag Function in SQL Server.

However, if you are using an earlier version of SQL Server which does not support LEAD and LAG function we can do the following:

WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
p.FirstName
FROM Person.Person p
)
SELECT
prev.FirstName PreviousValue,
CTE.FirstName,
nex.FirstName NextValue
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
GO

Though the result will be almost same as earlier queries but the performance will be very bad. I suggest you use SQL Server 2012 and the query related to the same.

Reference: Pinal Dave (https://blog.sqlauthority.com)