How do FIRST_VALUE and LAST_VALUE work, and why does LAST_VALUE often return unexpected results?
FIRST_VALUE returns the value from the first row of the window frame; LAST_VALUE returns the value from the last row. LAST_VALUE surprises most users because the default frame ends at CURRENT ROW, not at the end of the partition — the frame must be explicitly extended to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to reach the true last row.
How to think about it
Both functions look at the boundary rows of the current window frame — but they are not symmetric. FIRST_VALUE just works. LAST_VALUE almost always needs a frame clause fix, and missing that fix is one of the most common window function bugs in production.
FIRST_VALUE — straightforward
FIRST_VALUE picks the value from the first row of the ordered partition. The default frame starts at UNBOUNDED PRECEDING, so the first row is always in scope. You can write the frame explicitly for clarity, but it is not required.
LAST_VALUE — the frame trap
Here is where people get burned. The default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. For LAST_VALUE, that means the “last row” is just the current row — so every row returns its own value, which is almost never what you want.
The key insight
The frame clause controls which rows are visible to the function. FIRST_VALUE looks backward to the start of the partition — the default frame includes that. LAST_VALUE needs to look forward to the end of the partition — the default frame doesn’t reach there. Always add the explicit frame.