LAST_VALUE()

Syntax

How it works

LAST_VALUE() is a window function: it returns the value of an expression evaluated on the last row of the current window frame, while keeping every input row instead of collapsing them like an aggregate. It is the mirror image of FIRST_VALUE(), and both belong to the same family as NTH_VALUE().

Here is the single most important thing to understand, and the reason most LAST_VALUE() queries look broken. When you write an ORDER BY inside OVER() but do not specify a frame, SQL applies a default frame of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That frame ends at the current row, so LAST_VALUE() returns the value of the current row on every line, not the last row of the partition. It looks like the function does nothing useful.

To get the true last value of the whole partition you must widen the frame yourself with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. This tells the engine to look ahead to the final row of the partition. See the window functions guide for how frames work in general, and browse the full SQL functions library for related tools.

Examples

The default-frame trap: WRONG vs RIGHT side by side

-- WRONG: no frame, so the default RANGE ... CURRENT ROW
-- makes LAST_VALUE return the CURRENT row's amount
SELECT id, amount,
       LAST_VALUE(amount) OVER (ORDER BY id) AS wrong_last
FROM orders;

-- RIGHT: widen the frame to the whole partition
SELECT id, amount,
       LAST_VALUE(amount) OVER (
         ORDER BY id
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS right_last
FROM orders;

id | amount | wrong_last | right_last
---+--------+------------+-----------
 1 |    100 |        100 |        450
 2 |    250 |        250 |        450
 3 |    450 |        450 |        450

Latest status per entity

-- Show the most recent status next to every event row
SELECT ticket_id, changed_at, status,
       LAST_VALUE(status) OVER (
         PARTITION BY ticket_id
         ORDER BY changed_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS current_status
FROM ticket_history;

ticket_id | changed_at | status  | current_status
----------+------------+---------+---------------
       10 | 2026-01-02 | open    | closed
       10 | 2026-01-05 | pending | closed
       10 | 2026-01-09 | closed  | closed

Most recent order amount on every row

-- Every customer row carries their latest order amount
SELECT customer_id, order_date, amount,
       LAST_VALUE(amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS latest_amount
FROM orders;

customer_id | order_date | amount | latest_amount
------------+------------+--------+--------------
          7 | 2026-02-01 |    120 |           90
          7 | 2026-03-14 |    300 |           90
          7 | 2026-04-20 |     90 |           90

FIRST_VALUE with reversed ORDER BY: the simpler alternative

-- Same result as LAST_VALUE with a full frame, but no
-- frame clause needed: just reverse the sort and take
-- the first row. Often easier to read.
SELECT customer_id, order_date, amount,
       FIRST_VALUE(amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date DESC
       ) AS latest_amount
FROM orders;

customer_id | order_date | amount | latest_amount
------------+------------+--------+--------------
          7 | 2026-02-01 |    120 |           90
          7 | 2026-03-14 |    300 |           90
          7 | 2026-04-20 |     90 |           90

Common mistakes

-- The #1 LAST_VALUE bug: no frame clause.
-- The default frame ends at the CURRENT row, so this
-- returns the current row's value, not the last one.
SELECT id, amount,
       LAST_VALUE(amount) OVER (ORDER BY id) AS last_amount
FROM orders;

-- Add an explicit full frame so the window reaches
-- the final row of the partition.
SELECT id, amount,
       LAST_VALUE(amount) OVER (
         ORDER BY id
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_amount
FROM orders;

-- No ORDER BY, so there is no defined "last" row.
-- The result is arbitrary and engine dependent.
SELECT id, amount,
       LAST_VALUE(amount) OVER (PARTITION BY customer_id) AS last_amount
FROM orders;

-- ORDER BY gives the rows a meaningful sequence,
-- so "last" actually means something.
SELECT id, amount,
       LAST_VALUE(amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_amount
FROM orders;

-- Fighting with the frame clause when all you want
-- is the most recent value per group.
SELECT customer_id, amount,
       LAST_VALUE(amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS latest
FROM orders;

-- Reverse the sort and use FIRST_VALUE: no frame
-- clause, and the intent is clearer.
SELECT customer_id, amount,
       FIRST_VALUE(amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date DESC
       ) AS latest
FROM orders;

Performance

LAST_VALUE() requires the engine to build and sort each partition, so an index that matches your PARTITION BY and ORDER BY columns lets the optimiser avoid an extra sort step. Without a supporting index, large partitions mean a full sort in memory or spilling to disk.

Using the full frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is cheap once the partition is sorted, because the last row is already known. If you need the same last value on many rows, computing it once with a window function is usually faster than a correlated subquery that re-scans the table for every row. See the window functions guide for more.

Interview questions

SELECT customer_id, amount,
       LAST_VALUE(amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS latest
FROM orders;