Previous and next values
What if you want to shift the values in a column by one row up or down? You can use the exact same steps as in the previous exercise but with two new functions, LEAD()
, for the next value, and LAG()
, for the previous value. So you follow these steps:
- First, create partitions
- Then, order by a certain column
- Finally, use the
LEAD()
and/orLAG()
functions as per your requirement
This exercise is part of the course
Intermediate SQL Server
Exercise instructions
- Write a T-SQL query that for each territory:
- Shifts the values in
OrderDate
one row down. Call this columnPreviousOrder
. - Shifts the values in
OrderDate
one row up. Call this columnNextOrder
. You will need to PARTITION BY the territory
- Shifts the values in
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT TerritoryName, OrderDate,
-- Specify the previous OrderDate in the window
___(OrderDate)
-- Over the window, partition by territory & order by order date
___(___ BY ___ ___ BY ___) AS PreviousOrder,
-- Specify the next OrderDate in the window
___(OrderDate)
-- Create the partitions and arrange the rows
___ AS NextOrder
FROM Orders