Have you ever wondered how to access the previous and next rows in a SQL Server SELECT statement? In a previous blog post, we discussed a solution for this using CTE (Common Table Expressions) and the ROW_NUMBER function. However, that solution was not compatible with SQL Server 2000.

Thanks to our attentive readers, we realized that we missed the fact that SQL Server 2000 does not support the ROW_NUMBER function. In this blog post, we will provide an alternative solution that works with SQL Server 2000 and later versions.

Let’s consider the following SQL query:

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

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

USE AdventureWorks2012
GO
SELECT rownum = IDENTITY(INT, 1, 1), p.FirstName INTO #TempTable
FROM Person.Person p
ORDER BY p.BusinessEntityID;

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) using the IDENTITY function to generate a row number for each row. We then join the temporary table with itself to retrieve the previous and next rows based on the row number.

This solution provides the desired result:

PreviousValue | FirstName | NextValue
--------------|-----------|----------
NULL          | John      | Jane
John          | Jane      | Michael
Jane          | Michael   | NULL

We hope this solution helps you in accessing the previous and next rows in your SQL Server queries. By using the IDENTITY function and a temporary table, you can achieve this functionality even in SQL Server 2000.

Thank you for reading!