FIRST_VALUE (Transact-SQL)

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics yesAnalytics Platform System (PDW) YesAzure SQL Edge

Returns the first value in an ordered set of values.

Topic link icon 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 and earlier, 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) Preview), 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.

For more information, 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 AdventureWorks2012;
GO
SELECT Name, ListPrice,
       FIRST_VALUE(Name) OVER (ORDER BY ListPrice ASC) AS LeastExpensive
FROM Production.Product
WHERE ProductSubcategoryID = 37;

Here's 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 AdventureWorks2012;
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's a partial 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

See also