SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.