First value in a window
Suppose you want to figure out the first OrderDate in each territory or the last one. How would you do that? You can use the window functions FIRST_VALUE() and LAST_VALUE(), respectively! Here are the steps:
- First, create partitions for each territory
- Then, order by
OrderDate - Finally, use the
FIRST_VALUE()and/orLAST_VALUE()functions as per your requirement
This exercise is part of the course
Intermediate SQL Server
Exercise instructions
Write a T-SQL query that returns the first OrderDate by creating partitions for each TerritoryName.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT TerritoryName, OrderDate,
-- Select the first value in each partition
___(OrderDate)
-- Create the partitions and arrange the rows
OVER(PARTITION BY TerritoryName ___ OrderDate) AS FirstOrder
FROM Orders