The LAST_VALUE function in SQL is a powerful analytic function that allows you to easily retrieve the last value in a specific column within a group of rows. It can be used to find the latest record, the most recent value, or to compare the current row with the last row in a partition.

Syntax

The LAST_VALUE function has the following syntax:

LAST_VALUE(expression) OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column1, column2, ...
    [ROWS BETWEEN <window-frame> PRECEDING AND CURRENT ROW]
)

• expression: The column or expression used to determine the last value.
• PARTITION BY: Optional clause that divides the result set into partitions based on one or more columns.
• ORDER BY: Defines the order in which the rows within each partition will be evaluated.
• ROWS BETWEEN <window-frame> PRECEDING AND CURRENT ROW: Optional clause that defines the range of rows to consider within each partition.

Example

Let’s consider a table named sales with the following structure:

If we want to find the last order date for each product, we can use the LAST_VALUE function:

SELECT DISTINCT
    ProductName,
    LAST_VALUE(OrderDate) OVER (
        PARTITION BY ProductName
        ORDER BY OrderDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LastOrderDate
FROM
    sales;

This query will return the following result:

In this example, the LAST_VALUE function is used to retrieve the last order date for each product by ordering the rows by OrderDate within each partition of ProductName.

#SQL #Analytics