We have mentioned the LEAD and LAG functions announced with SQL Server 2012. In this article, we will examine the FIRST_VALUE function.
Now let’s look at the basic template:
|
1 2 |
FIRST_VALUE ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] ) |
The FIRST_VALUE function takes a value as a parameter. This value can be either a column or an expression. Order By and Partition By on the OVER Clause affects the value to be obtained.
In our examples, we used the AdventureWorks database that was create for SQL Server 2008R2.
Example 1:
When we run the below query, we will successfully obtain the first order date of the customer, whose CustomerID is 29994.
|
1 2 3 4 5 6 |
USE AdventureWorks2008R2; GO SELECT SalesOrderID,CustomerID,OrderDate,TotalDue, FIRST_VALUE(OrderDate) OVER (ORDER BY OrderDate) as FirstOrderDate FROM Sales.SalesOrderHeader WHERE CustomerID=29994 |
Example 2:
When we examine the query and the result, we group by year with PARTITION BY in the OVER Clause. Then we get the first order date for each year using the FIRST VALUE function.
In this example, there are two more points to consider:
- Partition By statement uses the YEAR function
- The value given to the First_Value function is obtained from the CONVERT function.
|
1 2 3 4 5 6 7 8 |
USE AdventureWorks2008R2; GO SELECT SalesOrderID,CustomerID, YEAR(OrderDate)as[Year],TotalDue, FIRST_VALUE(CONVERT(DATE,OrderDate)) OVER (Partition BY Year(OrderDate) ORDER BY OrderDate) AS FirstOrderDate FROM Sales.SalesOrderHeader WHERE CustomerID=29994 |
