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/or LAG() functions as per your requirement

Deze oefening maakt deel uit van de cursus

Intermediate SQL Server

Cursus bekijken

Oefeninstructies

Write a T-SQL query that for each territory:
- Shifts the values in OrderDate one row down. Call this column PreviousOrder.
- Shifts the values in OrderDate one row up. Call this column NextOrder. You will need to PARTITION BY the territory

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

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

Code bewerken en uitvoeren