How to Use the FIRST_VALUE() Function in SQL Server

In SQL Server, FIRST_VALUE() is a window function that returns the first value in an ordered set of values.

Basically, we can use it to get a value from the first row of a query result set or partition. This can be handy if we want to compare a value in the current row with a value in the first row, or use it as part of a calculation.

Example

Here’s an example of using the FIRST_VALUE() function:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    FIRST_VALUE( ProductPrice ) OVER ( 
        ORDER BY ProductPrice 
        ) AS FIRST_VALUE
FROM Products;

Result:

VendorId  ProductName                      ProductPrice  FIRST_VALUE
--------  -------------------------------  ------------  -----------
1004      Bottomless Coffee Mugs (4 Pack)  9.99          9.99       
1003      Hammock                          10            9.99       
1001      Long Weight (green)              11.99         9.99       
1004      Tea Pot                          12.45         9.99       
1001      Long Weight (blue)               14.75         9.99       
1001      Left handed screwdriver          25.99         9.99       
1001      Right handed screwdriver         25.99         9.99       
1002      Sledge Hammer                    33.49         9.99       
1003      Straw Dog Box                    55.99         9.99       
1003      Chainsaw                         245           9.99

Here, the FIRST_VALUE column displays the first value of the ProductPrice column when sorted in ascending order. That is, it returns the value from the first row.

Here’s an example of using this function to find out the price difference between the cheapest product and the current product:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    FIRST_VALUE( ProductPrice ) OVER ( 
        ORDER BY ProductPrice 
        ) AS FIRST_VALUE,
    ProductPrice - FIRST_VALUE( ProductPrice ) OVER ( 
        ORDER BY ProductPrice 
        ) AS Premium
FROM Products;

Result:

VendorId  ProductName                      ProductPrice  FIRST_VALUE  Premium
--------  -------------------------------  ------------  -----------  -------
1004      Bottomless Coffee Mugs (4 Pack)  9.99          9.99         0      
1003      Hammock                          10            9.99         0.01   
1001      Long Weight (green)              11.99         9.99         2      
1004      Tea Pot                          12.45         9.99         2.46   
1001      Long Weight (blue)               14.75         9.99         4.76   
1001      Left handed screwdriver          25.99         9.99         16     
1001      Right handed screwdriver         25.99         9.99         16     
1002      Sledge Hammer                    33.49         9.99         23.5   
1003      Straw Dog Box                    55.99         9.99         46     
1003      Chainsaw                         245           9.99         235.01

We sorted by the ProductPrice column in ascending order, therefore the first row contains the cheapest product. Therefore we were able to deduct that value from each subsequent row to find out the price premium of each subsequent product.

Partitioning the Result Set

As with any window function, we can use a PARTITION BY clause in order to partition the result set by a given column. For example, we can partition by vendor:

SELECT
    v.VendorName,
    p.ProductName,
    p.ProductPrice,
    FIRST_VALUE( ProductPrice ) OVER ( 
        PARTITION BY p.VendorId
        ORDER BY p.ProductPrice 
        ) AS FIRST_VALUE
FROM Products p 
INNER JOIN Vendors v
ON v.VendorId = p.VendorId;

Result:

VendorName     ProductName                      ProductPrice  FIRST_VALUE
-------------  -------------------------------  ------------  -----------
Mars Supplies  Long Weight (green)              11.99         11.99      
Mars Supplies  Long Weight (blue)               14.75         11.99      
Mars Supplies  Left handed screwdriver          25.99         11.99      
Mars Supplies  Right handed screwdriver         25.99         11.99      
Randy Roofers  Sledge Hammer                    33.49         33.49      
Pedal Medals   Hammock                          10            10         
Pedal Medals   Straw Dog Box                    55.99         10         
Pedal Medals   Chainsaw                         245           10         
Katty Kittens  Bottomless Coffee Mugs (4 Pack)  9.99          9.99       
Katty Kittens  Tea Pot                          12.45         9.99

Here we get the first value within each partition. Our FIRST_VALUE column resets with each new partition.

In this case I used a SQL join to join the Products and Vendors table so that I could present the vendor names instead of just their IDs.

The `OVER` Clause is Required

We must provide the OVER clause when using window functions such as FIRST_VALUE(). Here’s what happens if we remove it:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    FIRST_VALUE( ProductPrice )
FROM Products;

Result:

Msg 10753, Level 15, State 1, Line 5
The function 'FIRST_VALUE' must have an OVER clause.

The `ORDER BY` Clause is Required

Not only must we provide an OVER clause, but that clause must have an ORDER BY clause. Here’s what happens when we include an OVER clause without an ORDER BY clause:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    FIRST_VALUE( ProductPrice ) OVER( )
FROM Products;

Result:

Msg 4112, Level 15, State 1, Line 5
The function 'FIRST_VALUE' must have an OVER clause with ORDER BY.

Documentation

For more information and examples, see Microsoft’s documentation for the FIRST_VALUE() function.

Example

Partitioning the Result Set

The OVER Clause is Required

The ORDER BY Clause is Required

Documentation

The `OVER` Clause is Required

The `ORDER BY` Clause is Required