SQL Server 2012 introduced two new analytical functions: FIRST_VALUE() and LAST_VALUE(). These functions allow us to retrieve the first and last values from a list. In this blog post, we will explore these functions and understand their usage through examples.
Let’s start by using the AdventureWorks sample database, which is commonly used by developers for experimentation purposes. Consider the following query:
USE AdventureWorks
GO
SELECT s.SalesOrderID, s.SalesOrderDetailID, s.OrderQty,
FIRST_VALUE(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) AS FstValue,
LAST_VALUE(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) AS LstValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID, s.SalesOrderDetailID, s.OrderQty
GO
The above query will give us a result similar to the following:
| SalesOrderID | SalesOrderDetailID | OrderQty | FstValue | LstValue |
|---|---|---|---|---|
| … | … | … | … | … |
| … | … | … | … | … |
What’s interesting here is that as we go from row 1 to row 10, the value of the FIRST_VALUE() remains the same, but the value of the LAST_VALUE increases. This is because the LAST_VALUE represents the value of the current row and all the rows before it. To visualize this, refer to the figure provided in the original article.
While this may be useful in some cases, it becomes even more powerful when combined with the PARTITION BY clause. Consider the following query:
USE AdventureWorks
GO
SELECT s.SalesOrderID, s.SalesOrderDetailID, s.OrderQty,
FIRST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID ORDER BY SalesOrderDetailID) AS FstValue,
LAST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID ORDER BY SalesOrderDetailID) AS LstValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID, s.SalesOrderDetailID, s.OrderQty
GO
The above query will give us a result similar to the following:
| SalesOrderID | SalesOrderDetailID | OrderQty | FstValue | LstValue |
|---|---|---|---|---|
| … | … | … | … | … |
| … | … | … | … | … |
By using the PARTITION BY clause, we can create smaller windows of the resultset based on a specific column (in this case, SalesOrderID). The FIRST_VALUE() and LAST_VALUE() functions will then operate within these windows, providing us with more granular insights.
It’s important to note that these functions are not limited to numeric fields and can be applied to VARCHAR fields as well.
In future blog posts, we will delve deeper into the usage of these functions and explore more advanced scenarios where they can be applied.