Why LAST_VALUE surprises developers

Question

Accepted Answer

When you specify ORDER BY in OVER, the default window frame becomes RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The 'last' row in that frame is always the current row itself - so LAST_VALUE returns the current row's value. To get the last row across the full partition, you must explicitly write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. FIRST_VALUE does not have this problem because the first row of 'UNBOUNDED PRECEDING to CURRENT ROW' is always the same partition-first row.