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.

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

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

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

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

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

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)

Previous Post
SQL SERVER – UDF, UPDATE and More – Memory Lane – #047
Next Post
SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – Part 2

Related Posts

54 Comments. Leave new

  • Really useful…. Thanks you so much

    Reply
  • hello How can I get the next value on the condition of where?
    example where productID = 20 but I want 21
    thank you

    Reply
  • Kadir Kapadiya
    July 17, 2020 7:17 pm

    Want to update rows which are same continuously one by one. Like,

    Sno Name Activity StartTime EndTime
    1 ABC A 8:00:00 9:45:00
    1 ABC B 8:00:38 8:16:39
    1 ABC A 8:16:39 9:45:00
    2 ABC C 9:45:00 10:00:00
    3 ABC D 10:00:00 12:00:00
    3 ABC E 10:00:00 10:26:38
    3 ABC C 10:26:38 10:30:41
    3 ABC C 10:30:47 10:32:49
    3 ABC C 10:33:03 10:45:46

    You see in last Activity ‘C’ is coming continously 3 times. I want that row only once and need to update End Time to last row EndTime.

    Reply
  • This blog helped me quickly understand the LAG() function to move data down a row. Thanks!

    Reply

Leave a Reply

Menu