How do FIRST_VALUE and LAST_VALUE work, and why does LAST_VALUE often return unexpected results?

How to think about it

Both functions read a boundary row of the current window frame — but they aren’t symmetric. FIRST_VALUE just works; LAST_VALUE almost always needs a frame-clause fix, and forgetting it is one of the most common window-function bugs in production.

FIRST_VALUE — straightforward

FIRST_VALUE returns 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 — no frame clause needed:

-- the cheapest product in each category, shown on every row
SELECT name, category, price,
       FIRST_VALUE(name) OVER (
           PARTITION BY category ORDER BY price ASC
       ) AS cheapest_in_category
FROM products
ORDER BY category, price;

name	category	price	cheapest_in_category
Keyboard	Eng	80	Keyboard
Monitor	Eng	400	Keyboard
Laptop	Eng	1200	Keyboard
Lamp	Office	60	Lamp
Chair	Office	300	Lamp
Desk	Office	500	Lamp

cheapest_in_category is constant within each partition — Keyboard for every Eng row, Lamp for every Office row — because the first row (cheapest, by price ASC) is always inside the default frame.

A worked example — the LAST_VALUE frame trap

Here’s where people get burned. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so for LAST_VALUE the “last row” is just the current row — every row returns its own value. The fix is to extend the frame to the whole partition:

SELECT name, category, price,
       LAST_VALUE(name) OVER (
           PARTITION BY category ORDER BY price ASC
       ) AS most_expensive_broken,
       LAST_VALUE(name) OVER (
           PARTITION BY category ORDER BY price ASC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS most_expensive_fixed
FROM products
ORDER BY category, price;

name	category	price	most_expensive_broken	most_expensive_fixed
Keyboard	Eng	80	Keyboard	Laptop
Monitor	Eng	400	Monitor	Laptop
Laptop	Eng	1200	Laptop	Laptop
Lamp	Office	60	Lamp	Desk
Chair	Office	300	Chair	Desk
Desk	Office	500	Desk	Desk

Look at the two most_expensive columns side by side. The broken one changes on every row — it just echoes each row’s own name, because the frame stops at the current row. The fixed one is constant and correct — Laptop for Eng, Desk for Office — because UNBOUNDED FOLLOWING lets the function see all the way to the partition’s last (most expensive) row.

The idea underneath

The frame clause decides which rows the function can see. FIRST_VALUE looks backward to the partition’s start, which the default frame already includes — so it works untouched. LAST_VALUE needs to look forward to the partition’s end, which the default frame doesn’t reach. Always give LAST_VALUE the explicit frame.