SQL Server LAST_VALUE Function

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

SQL Server LAST_VALUE() function overview #

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

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

LAST_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 last row in an ordered partition of the result set. The scalar_expression can be a column, subquery, or expression 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 LAST_VALUE() function is applied. If you skip the PARTITION BY clause, the LAST_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 LAST_VALUE()function is applied.

rows_range_clause #

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

SQL Server LAST_VALUE() function examples #

We will use the sales.vw_category_sales_volume view created in the FIRST_VALUE() function tutorial to demonstrate how the LAST_VALUE()function works.

The following query returns data from the view:

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