SQL Keywords

SQL LAST_VALUE

What is the SQL LAST_VALUE function?

Returns the value of an expression from the last row in the current window frame.
Sign up to get up to date news on SQL keywords
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LAST_VALUE: PostgreSQL, MySQL 8.0+, SQL Server, Oracle, SQLite (3.25+), Snowflake, BigQuery, Redshift (with window support)

SQL LAST_VALUE Full Explanation

LAST_VALUE is a window (analytic) function that evaluates an expression for each row and returns the expression's value from the last row of the window frame defined by the OVER clause. Unlike aggregation functions, it does not collapse rows but instead adds a column with the result per row. The window frame defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, meaning the last row is the current row unless the frame is explicitly extended (e.g., ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING). Because LAST_VALUE is evaluated after the frame is applied, incorrect framing can lead to seemingly unexpected duplicates. NULLs are treated like any other value and ordering in the window clause dictates which row is considered "last."

SQL LAST_VALUE Syntax

LAST_VALUE ( value_expression )
OVER (
    [PARTITION BY partition_expression_list]
    ORDER BY order_expression_list
    [ROWS | RANGE BETWEEN frame_start AND frame_end]
);

SQL LAST_VALUE Parameters

  • value_expression (Any) - The column or expression to return from the last row.
  • partition_expression_list (Any) - Optional expressions that define partitions.
  • order_expression_list (Any) - Required expressions that define ordering within each partition.
  • frame_start / frame_end (Keywords) - Define the window frame (e.g., UNBOUNDED PRECEDING, CURRENT ROW, UNBOUNDED FOLLOWING).

Example Queries Using SQL LAST_VALUE

-- Show each employee's salary and the highest salary in their department
SELECT
  department_id,
  employee_id,
  salary,
  LAST_VALUE(salary) OVER (
    PARTITION BY department_id
    ORDER BY salary
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS max_department_salary
FROM employees;

-- Compare each order amount to the final order amount of the day
SELECT
  order_date,
  order_id,
  amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY order_date
    ORDER BY order_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS final_amount_today
FROM orders;

Expected Output Using SQL LAST_VALUE

  • A new column is added to each result row containing the value_expression from the last row in the window frame
  • In the first example, every row in a department shows the maximum salary for that department

Use Cases with SQL LAST_VALUE

  • Deriving max, min, or ending values without collapsing rows.
  • Comparing each row to the final status within a group (e.g., closing stock price of the day).
  • Forward filling future known values across preceding rows when combined with appropriate framing.

Common Mistakes with SQL LAST_VALUE

  • Forgetting to extend the frame to UNBOUNDED FOLLOWING, resulting in the current row's value returned instead of the true last row.
  • Omitting ORDER BY, which is mandatory in most dialects for LAST_VALUE.
  • Expecting LAST_VALUE to ignore NULLs; it does not skip them.
  • Confusing LAST_VALUE with aggregation function MAX; LAST_VALUE depends on window ordering, not value magnitude.

Related Topics

FIRST_VALUE, LAG, LEAD, NTH_VALUE, WINDOW FUNCTIONS, OVER clause

First Introduced In

SQL:2003

Frequently Asked Questions

What is the difference between LAST_VALUE and MAX?

MAX aggregates all rows and returns a single value per group, collapsing rows. LAST_VALUE is a window function that keeps all rows and returns the value from the last ordered row in the frame.

How do I get the true last row across the partition?

Specify the frame as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING in the OVER clause. This extends the frame to include all rows.

Does LAST_VALUE skip NULLs?

No. It treats NULL like any other value. If the last row in the frame has NULL, LAST_VALUE returns NULL.

Can I use LAST_VALUE without PARTITION BY?

Yes. If PARTITION BY is omitted, the entire result set is considered one partition.

Sign up to get up to date news on SQL keywords
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.
Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo

Check out other commonly used SQL Keywords!