Published on

September 13, 2013

Accessing Previous and Next Rows in SQL Server

Have you ever wondered how to access the previous and next rows in a SQL Server SELECT statement? In a previous blog post, I discussed a solution using Common Table Expressions (CTE). However, I received an email from a SQL Server user who does not have access to CTE and requested an alternative solution. In this blog post, I will provide a solution for those using SQL Server 2005 and later versions without CTE.

Let’s assume we have the following SQL query:

USE AdventureWorks2012
GO

SELECT p.FirstName
FROM Person.Person p
ORDER BY p.BusinessEntityID

The goal is to list the previous row and next row in the same SELECT statement. Here is the solution:

CREATE TABLE #TempTable (
    rownum INT,
    FirstName VARCHAR(256)
);

INSERT INTO #TempTable (rownum, FirstName)
SELECT rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID), p.FirstName
FROM Person.Person p;

SELECT prev.FirstName AS PreviousValue, TT.FirstName, nex.FirstName AS NextValue
FROM #TempTable TT
LEFT JOIN #TempTable prev ON prev.rownum = TT.rownum - 1
LEFT JOIN #TempTable nex ON nex.rownum = TT.rownum + 1;
GO

In the above example, we create a temporary table (#TempTable) and populate it with the row number and first name from the Person.Person table. Then, we join the #TempTable with itself to retrieve the previous and next rows based on the row number. The result will include the previous value, current value, and next value.

By using this solution, you can access the previous and next rows in a SQL Server SELECT statement without relying on CTE. This solution is compatible with SQL Server 2005 and later versions.

Thank you for reading this blog post. I hope you find this solution helpful in your SQL Server development.

UPDATE: I have made some corrections to the blog post based on user feedback. Thank you for your input!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.