What is the default window frame, and why can LAST_VALUE give a surprising answer?
With ORDER BY, the default frame runs from the first row to the current row, so LAST_VALUE returns the current row's value. To get the true last value, extend the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
Explain it simply
Imagine reading a list top to bottom but covering everything below your current line with a card. If someone asks, "what is the last name on the list?", you can only answer with the line you are on, because the rest is hidden.
Example
Table emp(name, salary) with salaries 50, 60, 70 for three people.
SELECT name, salary,
FIRST_VALUE(salary) OVER (ORDER BY salary) AS first_sal,
LAST_VALUE(salary) OVER (ORDER BY salary) AS last_sal_wrong,
LAST_VALUE(salary) OVER (
ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_sal_right
FROM emp;| salary | first_sal | last_sal_wrong | last_sal_right |
|---|---|---|---|
| 50 | 50 | 50 | 70 |
| 60 | 50 | 60 | 70 |
| 70 | 50 | 70 | 70 |
The default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. So the "last" row visible is the current one (or its ties). Without ORDER BY, the frame is the whole partition.
What to say in the interview
"With ORDER BY, the default frame stops at the current row, so LAST_VALUE just echoes the current value. I widen the frame to UNBOUNDED FOLLOWING, or use FIRST_VALUE with a descending order."
Common mistakes
- Trusting LAST_VALUE without checking the frame.