SQL Server FIRST_VALUE Function

Summary: in this tutorial, you will learn how to use the SQL Server FIRST_VALUE() function to get the first value in an ordered partition of a result set.

SQL Server FIRST_VALUE() function overview

The FIRST_VALUE() function is a window function that returns the first value in an ordered partition of a result set.

The following shows the syntax of the FIRST_VALUE() function:

FIRST_VALUE ( scalar_expression )  
OVER ( 
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
    Code language: SQL (Structured Query Language) (sql)

In this syntax:

scalar_expression

scalar_expression is an expression evaluated against the value of the first row of the ordered partition of a result set. The scalar_expression can be a column, subquery, or expression that evaluates to a single value. It cannot be a window function.

PARTITION BY clause

The PARTITION BY clause distributes rows of the result set into partitions to which the FIRST_VALUE() function is applied. If you skip the PARTITION BY clause, the FIRST_VALUE() function will treat the whole result set as a single partition.

ORDER BY clause

The ORDER BY clause specifies the logical order of the rows in each partition to which the FIRST_VALUE()function is applied.

rows_range_clause

The rows_range_clause further limits the rows within the partition by defining start and end points.

SQL Server FIRST_VALUE() function examples

The following statement creates a new view named sales.vw_category_sales_volume that returns the number of products sold by product category and year.

CREATE VIEW 
    sales.vw_category_sales_volume 
AS
SELECT 
    category_name, 
    YEAR(order_date) year, 
    SUM(quantity) qty
FROM 
    sales.orders o
INNER JOIN sales.order_items i 
    ON i.order_id = o.order_id
INNER JOIN production.products p 
    ON p.product_id = i.product_id
INNER JOIN production.categories c 
    ON c.category_id = p.product_id
GROUP BY 
    category_name, 
    YEAR(order_date);
Code language: SQL (Structured Query Language) (sql)

Here is the data from the view:

SELECT 
    *
FROM 
    sales.vw_category_sales_volume
ORDER BY 
    year, 
    category_name, 
    qty;
Code language: SQL (Structured Query Language) (sql)

A) Using FIRST_VALUE() over a result set example

This example uses FIRST_VALUE() function to return category name with the lowest sales volume in 2017:

SELECT 
    category_name,
    year,
    qty,
    FIRST_VALUE(category_name) OVER(
        ORDER BY qty
    ) lowest_sales_volume
FROM 
    sales.vw_category_sales_volume
WHERE
    year = 2017;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example:

• The PARTITION BY clause was not specified therefore the whole result set was treated as a single partition.
• The ORDER BY clause sorted rows in each partition by quantity (qty) from low to high.

B) Using FIRST_VALUE() over partitions example

The following example uses the FIRST_VALUE() function to return product categories with the lowest sales volumes in 2016 and 2017.

SELECT 
    category_name,
    year,
    qty,
    FIRST_VALUE(category_name) OVER(
        `PARTITION BY` year
        ORDER BY qty
    ) lowest_sales_volume
FROM 
    sales.vw_category_sales_volume
WHERE
    year BETWEEN 2016 AND 2017;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

In this example:

• The PARTITION BY clause distributed rows by year into two partitions, one for 2016 and the other for 2017.
• The ORDER BY clause sorted rows in each partition by quantity (qty) from low to high.
• The FIRST_VALUE() function is applied to each partition separately. For the first partition, it returned Electric Bikes and for the second partition it returned Comfort Bicycles because these categories were the first rows in each partition.

In this tutorial, you have learned how to use the SQL Server FIRST_VALUE() function to return the first value in an ordered partition of a result set.