Grabbing the edges
FIRST VALUE returns the value from the first row of the window frame, and LAST VALUE returns the value from the last row. They are handy for stamping every row with the opening or closing value of its partition, such as the first order date per customer.
The LAST VALUE trap
A common surprise: LAST VALUE often returns the current row instead of the partition's true last row. The reason is the default frame. When you write ORDER BY, the implicit frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. So LAST VALUE only sees rows up to the current one, and the last visible row is the current row itself.
The fix
To make LAST VALUE see the whole partition, widen the frame explicitly:
- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
FIRST VALUE rarely surprises because the first row is already inside the default frame. Many engineers reach for MAX or MIN, or use NTH VALUE, when they really want a true boundary value across the full partition.
Key idea
FIRST VALUE and LAST VALUE read the frame edges, but the default frame stops at the current row, so LAST VALUE needs an explicit unbounded following frame to reach the real partition end.