LAST_VALUE

The SQL LAST_VALUE() function is a window function that retrieves the last value in a set of ordered rows within a partition. The function is typically used in conjunction with the OVER() clause to specify the partition and order criteria for the set of rows.

Syntax

The syntax for the LAST_VALUE() function is as follows:

LAST_VALUE(expression) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC|DESC], ...
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

In the syntax above, the expression parameter specifies the column or expression that the function should evaluate on each row. The PARTITION BY clause divides the result set into partitions, while the ORDER BY clause determines the order in which the rows are processed. The ROWS BETWEEN clause specifies the range of rows within the partition to include in the calculation.

The LAST_VALUE() function returns the last value of the expression for each row within the specified partition and order. If the function encounters a NULL value, it will return NULL unless the IGNORE NULLS option is specified.

Example

Here is an example of how to use the LAST_VALUE() function to retrieve the last sales amount for each salesperson in a sales table:

SELECT salesperson, date, amount,
       LAST_VALUE(amount) OVER (
           PARTITION BY salesperson
           ORDER BY date
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) as last_amount
FROM sales
ORDER BY salesperson, date;

In this example, the LAST_VALUE() function is used to retrieve the last sales amount for each salesperson in the sales table. The function is partitioned by salesperson and ordered by date. The ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING clause specifies that the calculation should include all rows in each partition. The result set includes columns for salesperson, date, amount, and last_amount.

In summary, the SQL LAST_VALUE() function is a powerful tool for retrieving the last value in a set of ordered rows within a partition. It can be used in a wide range of scenarios, such as calculating running totals, identifying maximum values, and more.