LAST_VALUE returns each row's own salary instead of the department's top salary
This query should place each department's highest salary on every row, but top_salary just repeats each row's own salary instead. Find the cause and fix it.
SELECT employee_id, department, salary,
LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) AS top_salary
FROM employees;Find and fix the bug.
With ORDER BY, the default RANGE frame ends at the current row, so LAST_VALUE returns that row's own salary. Widen it to UNBOUNDED FOLLOWING, or just use MAX(salary) OVER (PARTITION BY department).
- ✗Blaming the sort direction rather than the frame default
- ✗Thinking
PARTITION BYrestarts per row instead of per group - ✗Expecting
LAST_VALUEto scan the whole partition by default
- →Why does
FIRST_VALUEwork as expected here butLAST_VALUEdoesn't? - →When would you prefer
MAX() OVER (...)overLAST_VALUE?
The cause is the default frame. As soon as a window has an ORDER BY, its frame becomes RANGE UNBOUNDED PRECEDING AND CURRENT ROW, ending at the current row. So the "last value" is always the current row itself.
Widen the frame to the end of the partition:
SELECT employee_id, department, salary,
LAST_VALUE(salary) OVER (
PARTITION BY department ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS top_salary
FROM employees;Simpler and safer is MAX(salary) OVER (PARTITION BY department), which needs no frame at all. Changing the sort direction or dropping the partition does not fix the problem.
