SQL Server FIRST_VALUE() Function

The FIRST_VALUE() function is a window function that returns the first value from a window. This function was first introduced in SQL Server 2012.

Syntax

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

scalar_expression: Required. The column or expression from which to return the first value.
partition_by_clause: Optional. The column or expression that partitions the window into partitions.
order_by_clause: Required. The column or expression that specifies the order of the rows.
rows_range_clause: Optional. The number of rows to include in the window.

Use Cases

The FIRST_VALUE() function is commonly used in the following scenarios:

To return the first value in a group.
To get the first value in a window for further calculations.

Examples

Example 1

Consider an employee table that contains employee IDs, names, and sales amounts. We want to query the top salesperson in each department.

SELECT Department,
  FIRST_VALUE(Name) OVER (
    PARTITION BY Department
    ORDER BY Sales DESC
  ) AS TopSalesperson
FROM EmployeeSales;

Output:

Department	TopSalesperson
Marketing	Alice
Sales	Bob
Engineering	Charlie

Example 2

Consider a sales table that contains sales dates, salespersons, and sales amounts. We want to calculate the sales-to-first-sale ratio for each salesperson.

SELECT Salesperson,
  Sales,
  FIRST_VALUE(Sales) OVER (
    PARTITION BY Salesperson
    ORDER BY SaleDate
  ) AS FirstSale,
  Sales / FIRST_VALUE(Sales) OVER (
    PARTITION BY Salesperson
    ORDER BY SaleDate
  ) AS SalesRatio
FROM SalesTable;