Have you ever wondered how to access the previous and next row values in a SELECT statement in SQL Server? This question was brought to my attention recently, and I thought it would be a great topic to discuss in this blog post.

In SQL Server 2012, the LEAD and LAG functions were introduced as analytic functions to help us easily access the previous and next row values. Let’s take a look at an example to understand how these functions work.

Suppose we have a table called “Person” in the AdventureWorks2012 database, and we want to retrieve the first names of the people in the table, along with their previous and next row values.

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

As you can see, by using the LAG and LEAD functions, we can easily retrieve the previous and next row values for each row in the result set. This makes it convenient to perform calculations or comparisons based on the values of adjacent rows.

However, if you are using an earlier version of SQL Server that does not support the LEAD and LAG functions, there is still a way to achieve the same result. We can make use of the ROW_NUMBER function and self-joins to get the previous and next row values.

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

Although this alternative approach will give us the same result, it is important to note that the performance may be significantly worse compared to using the LEAD and LAG functions. Therefore, if you have the option to use SQL Server 2012 or a newer version, I highly recommend using the query with the LEAD and LAG functions for better performance.

In conclusion, accessing previous and next row values in a SELECT statement can be easily achieved using the LEAD and LAG functions introduced in SQL Server 2012. However, if you are working with an older version of SQL Server, you can still accomplish the same result using the ROW_NUMBER function and self-joins, although the performance may be impacted.

I hope you found this blog post helpful in understanding how to access previous and next row values in SQL Server. If you have any questions or comments, feel free to leave them below.