FIRST_VALUE (Transact-SQL)

Returns the first value in an ordered set of values that is specified by an argument list.

Syntax

FIRST_VALUE ( [ scalar_expression ] )
    OVER ( [ <partition_by_clause> ] <order_by_clause> [ ,...n ] )
            

Parameters

scalar_expression

The expression to which the FIRST_VALUE is applied. It can be a column name, a constant, or any other scalar expression.

Returns

The data type of scalar_expression.

Remarks

• FIRST_VALUE is a deterministic function.
• If two or more rows share the same value for the expression that determines the first row, then all these rows will be returned as the first value.
• FIRST_VALUE can only be used with the OVER clause.
• In a OVER clause, ORDER BY is required to determine the order of rows. If ORDER BY is not specified, the result is nondeterministic.

Examples

WITH SalesWithRank AS (
    SELECT
        ProductName,
        Category,
        SalesDate,
        ROW_NUMBER() OVER(PARTITION BY Category ORDER BY SalesDate ASC) as rn
    FROM Products
)
SELECT
    ProductName,
    Category,
    SalesDate,
    FIRST_VALUE(ProductName) OVER(PARTITION BY Category ORDER BY SalesDate ASC) as FirstProductSoldInCategory
FROM SalesWithRank
WHERE rn = 1;
                

SELECT
    Department,
    Salary,
    EmployeeName,
    FIRST_VALUE(Salary) OVER(PARTITION BY Department ORDER BY Salary DESC) as HighestSalaryInDepartment
FROM Employees;