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!