LAST_VALUE() in SQL

Returns the last value in an ordered partition of a result set.

Syntax

LAST_VALUE(expression) OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
    [frame_clause]
)

Return type

Same as input expression type

LAST_VALUE() Function Example

SELECT product_name, sales_amount,
       LAST_VALUE(sales_amount) OVER (ORDER BY sales_date 
       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_sale
FROM sales;
-- Output: 'Product A', 1000, 1200 (1200 being last sale)

What is LAST_VALUE() in SQL?

The LAST_VALUE() function in SQL returns the last value within an ordered partition of a result set. It is commonly used for retrieving the most recent record, identifying the latest transaction, and comparing current values to the last value in a dataset.

This function is supported in SQL Server, PostgreSQL, MySQL 8.0+, and Oracle as part of window functions.

This function is widely used in trend analysis, financial reporting, and tracking the most recent data points.

Parameters:

expression: Column or expression to retrieve
frame_clause (recommended): Defines window frame boundaries

Example Use Cases:

-- Compare to worst performing product

SELECT category, product_name, sales_amount,
       LAST_VALUE(product_name) OVER (
           PARTITION BY category 
           ORDER BY sales_amount 
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS lowest_selling_product
FROM product_sales;
-- Output:
category    | product_name    | sales_amount | lowest_selling_product
------------|----------------|--------------|----------------------
Electronics | iPhone 13      | 125000       | Pixel 6
Electronics | Galaxy S21     | 95000        | Pixel 6
Electronics | Pixel 6        | 75000        | Pixel 6
Clothing    | Winter Jacket  | 45000        | T-Shirt
Clothing    | Jeans         | 35000        | T-Shirt
Clothing    | T-Shirt       | 25000        | T-Shirt
Books       | Harry Potter   | 15000        | The Hobbit
Books       | Lord of Rings | 12000        | The Hobbit
Books       | The Hobbit    | 10000        | The Hobbit

-- Track closing price

SELECT stock_symbol, trading_date, price,
       LAST_VALUE(price) OVER (
           PARTITION BY stock_symbol 
           ORDER BY trading_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS latest_price
FROM stock_prices;
-- Output:
stock_symbol | trading_date | price  | latest_price
-------------|--------------|--------|-------------
AAPL         | 2024-01-01  | 185.50 | 186.75
AAPL         | 2024-01-02  | 187.25 | 186.75
AAPL         | 2024-01-03  | 186.75 | 186.75
GOOGL        | 2024-01-01  | 140.25 | 141.75
GOOGL        | 2024-01-02  | 142.50 | 141.75
GOOGL        | 2024-01-03  | 141.75 | 141.75
MSFT         | 2024-01-01  | 375.00 | 376.25
MSFT         | 2024-01-02  | 377.50 | 376.25
MSFT         | 2024-01-03  | 376.25 | 376.25

-- Final score in competition

SELECT competitor_name, round_number, score,
       LAST_VALUE(score) OVER (
           PARTITION BY competitor_name 
           ORDER BY round_number
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS final_score
FROM competition_results;
-- Output:
competitor_name | round_number | score | final_score
----------------|--------------|-------|-------------
John Smith      | 1           | 85    | 92
John Smith      | 2           | 88    | 92
John Smith      | 3           | 92    | 92
Maria Garcia   | 1           | 90    | 91
Maria Garcia   | 2           | 87    | 91
Maria Garcia   | 3           | 91    | 91
James Brown    | 1           | 82    | 89
James Brown    | 2           | 86    | 89
James Brown    | 3           | 89    | 89

Notes:

Behavior: Returns NULL if partition is empty
Performance Considerations: Window frame specification important for correct results
Version Info: Core window function available in most modern DBMS
Deprecated/Recommended Alternatives: None, standard functionality

Error Handling:

Error: May give unexpected results without proper frame clause
Recommendation: Always specify frame clause ROWS/RANGE BETWEEN

Supported Databases:

DBMS

Function / Syntax