Common mistake

SQL: Why LAST_VALUE Returns the Current Row

With an ORDER BY and no frame, a window stops at the current row, so LAST_VALUE never sees the rows after it. Extend the frame to the end, or use FIRST_VALUE.

The symptom

You want every product shown beside the most expensive product in its category. Sort each category by price and take the last one: last_value(product_name) over (partition by category order by price). Instead of repeating one name down each category, the column just copies each row's own product name.

Why it happens

A window function only sees the rows in its frame. When the window has an ORDER BY and you don't name a frame, the default is range between unbounded preceding and current row: from the first row up to the current one. For the cheapest product the frame is one row, so the "last value" is itself. For the next product it's two rows, and the last of those is, again, itself. The most expensive product is always past the end of the frame, except on its own row.

first_value() doesn't have this problem, because the start of the default frame is the first row of the partition. That asymmetry is why this trap catches people who have used first_value() without trouble.

The fix

Name a frame that runs to the end of the partition: rows between unbounded preceding and unbounded following. The fixed query also adds product_name to the window's ORDER BY, so two products at the same price can't make the answer depend on the engine.

Wrong: default frame ends at this row

select c.category_name,
       p.product_name,
       p.price,
       last_value(p.product_name) over (
         partition by c.category_name
         order by p.price
       ) as priciest_in_category
from products as p
join categories as c on c.category_id = p.category_id
where c.category_name in ('Houseplants', 'Tools')
order by c.category_name, p.price

Right: frame runs to the last row

select c.category_name,
       p.product_name,
       p.price,
       last_value(p.product_name) over (
         partition by c.category_name
         order by p.price, p.product_name
         rows between unbounded preceding and unbounded following
       ) as priciest_in_category
from products as p
join categories as c on c.category_id = p.category_id
where c.category_name in ('Houseplants', 'Tools')
order by c.category_name, p.price

With the full frame, every Houseplants row names Fiddle-Leaf Fig and every Tools row names Pruning Shears. The default frame only gets those two rows right, because each is the last row of its own frame.

Often simpler: flip the sort

first_value(product_name) over (partition by category order by price desc, product_name) gives the same answer with the default frame, since the most expensive product is now the first row. max(price) over (partition by category) is simpler still when you only need the price, not the product.