FIRST_VALUE (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Azure SQL Edge
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Returns the first value in an ordered set of values.
Transact-SQL syntax conventions
Syntax
FIRST_VALUE ( [ scalar_expression ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
scalar_expression
The value to be returned. scalar_expression can be a column, subquery, or other arbitrary expression that results in a single value. Other analytic functions aren't permitted.
[ IGNORE NULLS | RESPECT NULLS ]
Applies to: SQL Server (starting with SQL Server 2022 (16.x)), Azure SQL Database, Azure SQL Managed Instance, Azure SQL Edge
IGNORE NULLS
- Ignore null values in the dataset when computing the first value over a partition.
RESPECT NULLS
- Respect null values in the dataset when computing first value over a partition. RESPECT NULLS
is the default behavior if a NULLS option isn't specified.
For more information on this argument in Azure SQL Edge, see Imputing missing values.
OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
The partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.
The order_by_clause determines the logical order in which the operation is performed. The order_by_clause is required.
The rows_range_clause further limits the rows within the partition by specifying start and end points.
For more information, see OVER Clause (Transact-SQL).
Return types
The same type as scalar_expression.
Remarks
FIRST_VALUE
is nondeterministic. For more information, see Deterministic and nondeterministic functions.
Examples
A. Use FIRST_VALUE over a query result set
The following example uses FIRST_VALUE
to return the name of the product that is the least expensive in a given product category.
USE AdventureWorks2022;
GO
SELECT Name,
ListPrice,
FIRST_VALUE(Name) OVER (
ORDER BY ListPrice ASC
) AS LeastExpensive
FROM Production.Product
WHERE ProductSubcategoryID = 37;
Here is the result set.
Name ListPrice LeastExpensive
----------------------- --------------------- --------------------
Patch Kit/8 Patches 2.29 Patch Kit/8 Patches
Road Tire Tube 3.99 Patch Kit/8 Patches
Touring Tire Tube 4.99 Patch Kit/8 Patches
Mountain Tire Tube 4.99 Patch Kit/8 Patches
LL Road Tire 21.49 Patch Kit/8 Patches
ML Road Tire 24.99 Patch Kit/8 Patches
LL Mountain Tire 24.99 Patch Kit/8 Patches
Touring Tire 28.99 Patch Kit/8 Patches
ML Mountain Tire 29.99 Patch Kit/8 Patches
HL Road Tire 32.60 Patch Kit/8 Patches
HL Mountain Tire 35.00 Patch Kit/8 Patches
B. Use FIRST_VALUE over partitions
The following example uses FIRST_VALUE
to return the employee with the fewest number of vacation hours compared to other employees with the same job title. The PARTITION BY
clause partitions the employees by job title and the FIRST_VALUE
function is applied to each partition independently. The ORDER BY
clause specified in the OVER
clause determines the logical order in which the FIRST_VALUE
function is applied to the rows in each partition. The ROWS UNBOUNDED PRECEDING
clause specifies the starting point of the window is the first row of each partition.
USE AdventureWorks2022;
GO
SELECT JobTitle,
LastName,
VacationHours,
FIRST_VALUE(LastName) OVER (
PARTITION BY JobTitle ORDER BY VacationHours ASC ROWS UNBOUNDED PRECEDING
) AS FewestVacationHours
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID
ORDER BY JobTitle;
Here is the result set.
JobTitle LastName VacationHours FewestVacationHours
----------------------------------- ------------------------- ------------- -------------------
Accountant Moreland 58 Moreland
Accountant Seamans 59 Moreland
Accounts Manager Liu 57 Liu
Accounts Payable Specialist Tomic 63 Tomic
Accounts Payable Specialist Sheperdigian 64 Tomic
Accounts Receivable Specialist Poe 60 Poe
Accounts Receivable Specialist Spoon 61 Poe
Accounts Receivable Specialist Walton 62 Poe