LAG and LEAD

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Read adjacent values in a declared order without a self join.

Run the example

SELECT TOP (5) OrderID, OrderDate,
       LAG(OrderDate) OVER (ORDER BY OrderID) AS PreviousDate,
       LEAD(OrderDate) OVER (ORDER BY OrderID) AS NextDate
FROM dbo.Orders
ORDER BY OrderID;

Read it step by step

1. LAG reads the preceding order by OrderID; LEAD reads the following order.
2. The first previous value is NULL because no earlier row exists.
3. Window values are calculated before TOP limits the displayed five rows.

Check the result

The fifth displayed row can still have a NextDate from the sixth order.

Practice

Show Products 1–5 with the preceding product’s ListPrice in ProductID order.

SELECT TOP (5) ProductID, ListPrice,
       LAG(ListPrice) OVER (ORDER BY ProductID) AS PreviousPrice
FROM dbo.Products
ORDER BY ProductID;