LAST_VALUE()
SQL · Window Functions
Theory
LAST_VALUE(column) returns the value from the last row of the window frame. This sounds like the opposite of FIRST_VALUE, but there is a trap. When the window has an ORDER BY and no frame, the default frame ends at the current row (and its peers). So the 'last row of the frame' is usually the current row itself, and LAST_VALUE just echoes each row's own value.
To get the true last row of the partition, write the frame explicitly: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. With PARTITION BY dept ORDER BY salary DESC and that frame, LAST_VALUE(name) gives the lowest earner in each department on every row. An often simpler alternative is FIRST_VALUE with the sort order reversed (ORDER BY salary ASC), which needs no frame. Related: NTH_VALUE(column, n) returns the n-th row of the frame and has the same frame rule.
Why we use it
Sometimes you need the value from the end of an ordered group: the latest status, the closing price, the lowest earner. LAST_VALUE does this without a subquery, as long as you set the frame to cover the whole partition.
When to use it
Use LAST_VALUE with an explicit UNBOUNDED FOLLOWING frame when you need the last row's value. Prefer FIRST_VALUE with the opposite ORDER BY when you want something easier to read and harder to get wrong.
How it works
- Rows split by PARTITION BY and sorted by ORDER BY
- Frame has UNBOUNDED FOLLOWING? Yes: Frame = whole partition. No: Frame stops at current row.
- Read the column from the last row of the frame
- Used the default frame? Yes: Usually the row's own value (bug). No: True last value.
- Value shown on each row
Syntax
LAST_VALUE(column) OVER (
ORDER BY col
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)Example
SELECT name, department, salary,
LAST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_earner
FROM employees;Key points
- Needs an explicit UNBOUNDED FOLLOWING frame; the default stops at the current row
- A common source of confusing results if the frame is left default
- Alternative: FIRST_VALUE with the ORDER BY reversed
- NTH_VALUE(col, n) follows the same frame rules
- Add a tie-breaker so the last row is well defined