First_Value SQL Analytic Function in SQL Server 2012 T-SQL

First_Value SQL analytic function returns the first value of an ordered set of values.
SQL First_Value() function can be used with Partition By clause. Using Partition By clause enables developers to group the return set into smaller subsets and apply the First_Value() function over these smaller partitioned subsets.

Further more, with the latest enhancements on OVER() clause, it is possible to limit the rows that the SQL Server First_Value() function will be applied with rows_range_clause. I'll try to show T-SQL developers how they can use rows_range_clause with SQL sample codes in this tutorial.

Here is the T-SQL syntax for SQL First_Value() analytic function:


FIRST_VALUE (scalar_expression) OVER ([partition_by_clause] order_by_clause [rows_range_clause])

Code

The scalar expression can now be a calculation or an expression, or a subquery that returns a single value besides a column. Scalar expression in First_Value() is obligatory

Partition By clause is optional. It is used to group the result set into subsets that the First_Value() analytic function will be applied.

Order By clause is obligatory

Rows Range clause is optional in SQL First_Value() function. T-SQL developers can further limit the effect of the analytic function. For example appying the function over previous three rows, etc. This rows_range_clause enhancement on Over() clause is marvellous, and I believe it will be very handy tool for SQL programmers.

A basic First_Value() function can be implemented using ROW_NUMBER() and fetching only the value where Row_Number value is 1. But in some sql queries the requirements may be more complex to use sub-select or CTE statements where Row_Number is used and filtering rows with row number is 1. SQL First_Value() analytic function can also be thought like a SQL Server MAX() aggregation function too. But it has more than that. And when the additional enhancements like rows_range_clause is considered, it is nearly impossible to implement such solutions so easily on SQL Server systems prior to SQL Server 2012, Denali CTP3. So the requirement to analytic functions like First_Value in SQL Server is obvious.

SQL First_Value() Function Samples

Just like other SQL Server analytic function, T-SQL First_Value function is first introduced to SQL programmers with SQL Server 2012 release. In this SQL tutorial, I'll try to give sample tsql codes showing how to use First_Value SQL analytic function on SQL Server 2012 sample database.

First Value and Last Value using First_Value() Function

Following t-sql select statement returns a list of sales orders whose order date is in the last month period. (Why not use EOMonth function here?)

Besides sales order data, FirstOrder column will display the oldest order on all rows of the return set. In order to display oldest order, or first order in the ordered set, FIRST_VALUE function is used with ORDER BY OrderDate clause.

Similar to FirstOrder column, the LastOrder column displays the last order number of the return result set using First_Value() analytic function. Is it amazing to get last value using First_Value() function, is not it? It is just because of descending ordering in ORDER BY clause.


SELECT

 FIRST_VALUE(SalesOrderID) OVER (ORDER BY OrderDate ASC) as FirstOrder,

 FIRST_VALUE(SalesOrderID) OVER (ORDER BY OrderDate DESC) as LastOrder,

 *

FROM Sales.SalesOrderHeader

WHERE OrderDate > DATEADD(mm, -1, '20080731')

Code

First_Value SQL analytic function in SQL Server 2012

First_Value() Function and Partition By Clause

Another sample T-SQL SELECT statement lists all Sales Orders while displaying the customers' first order and last order number. In order to fetch first order number and last order number for the customer, instead of using ROW_NUMBER() function I used new SQL Server Analytic function FIRST_VALUE() in combination with PARTITION BY clause. By partitioning according to Customer and ordering the rows ascending and descending, T-SQL developers can get the first and last sales order number.


SELECT

 SalesOrderNumber,

 CustomerID,

 OrderDate,

 FIRST_VALUE(SalesOrderNumber) OVER (PARTITION BY CustomerID ORDER BY OrderDate ASC) [Customer First Order],

 FIRST_VALUE(SalesOrderNumber) OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) [Customer Last Order]

FROM Sales.SalesOrderHeader

ORDER BY CustomerID, OrderDate

Code

SQL Server 2012 Denali first_value analytic function

Get Maximum Value using First_Value() Function

Recently in a T-SQL forum, I read a question how to select the maximum salary in a rows of table without using MAX() aggregation function.

Here is a few methods including new T-SQL First_Value() function to select maximum value in a table field.


-- Using SQL Server Max() aggregation function

SELECT MAX(Rate) from HumanResources.EmployeePayHistory



-- Using First_Value() SQL analytic function

SELECT TOP 1 First_Value(Rate) OVER (Order By Rate Desc) from HumanResources.EmployeePayHistory



-- Using TOP 1 with ORDER BY clause

SELECT TOP 1 Rate from HumanResources.EmployeePayHistory Order By Rate Desc



-- Using SET ROWCOUNT and ORDER BY clause

SET RowCount 1

SELECT Rate from HumanResources.EmployeePayHistory Order By Rate Desc

SET RowCount 0

Code

All above sample SELECT statements can be used where ever possible, but using the new First_Value() SQL analytic function is a requirement in some special cases.

For more SQL Analytic Functions new in SQL Server 2012 for T-SQL programmers with Denali CTP3, please refer to referred tutorial.