FIRST_VALUE() and LAST_VALUE()
Show each employee alongside the highest- and lowest-paid peer in their department.
Overview
FIRST_VALUE(col) and LAST_VALUE(col) return the first and last value of
col within the window frame. They're handy for comparing each row to the
extremes of its group — e.g., each employee vs their department's top and bottom
earner. Note the default frame: you usually must extend it to the partition end
for a true "last".
The Query
Employees & Departments
Loading database engine...
Step-by-Step Breakdown
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING— expands the frame to the whole partition soFIRST/LASTsee all peers, not just up to the current row.FIRST_VALUE(salary)— the department's highest salary (top of theORDER BY salary DESC).LAST_VALUE(salary)— the department's lowest salary.
LAST_VALUE default frame trap
By default LAST_VALUE only sees rows up to the current one, so it often
returns the current row itself. Always widen the frame for a true partition
end.
Variations
Compare each movie to the highest-budget film in its genre:
Movies (Movies/Actors/Ratings)
Loading database engine...
Common Mistakes
- Forgetting to widen the frame for LAST_VALUE. The default frame makes
LAST_VALUEreturn the current row. - Confusing order direction. "First" and "last" depend entirely on the
ORDER BYinside the window. - Performance. Explicit unbounded frames are fine, but very large partitions with wide frames can be costly.