How does FIRST_VALUE(product) OVER (ORDER BY price) differ from MIN(price) OVER ()?
answer
- One returns a value, the other a row's attribute
- The ordering key need not be the returned column
- Think "which row", not "what number"
- Ties make the chosen row undetermined
- Only one of the two obeys the frame
basics
~20 sMIN returns the smallest value of its own argument. FIRST_VALUE returns any column you name, taken from whichever row sorts first — so it answers "which product is cheapest", not just "what is the cheapest price".
solid answer
~40 s`MIN(price)` reduces one column to its extreme value; it cannot tell you which row that value came from. `FIRST_VALUE(product)` picks a row by the window's `ORDER BY` and returns that row's `product` — the argument and the ordering key are independent, which is exactly what makes it useful. So `FIRST_VALUE(product) OVER (PARTITION BY category ORDER BY price)` names the cheapest product per category, something no aggregate can express directly. Two differences follow. `FIRST_VALUE` is frame-sensitive, so a narrow frame such as `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` changes which row it reads, while `MIN` over an unordered window always sees the whole partition. And when the ordering key has ties, `FIRST_VALUE` may return either tied row — add a tiebreaker to the window `ORDER BY` when determinism matters.
code
sql · 6 linesSELECT category,
product,
price,
MIN(price) OVER (PARTITION BY category) AS cheapest_price,
FIRST_VALUE(product) OVER (PARTITION BY category ORDER BY price, product_id) AS cheapest_product
FROM products;go deeper
Be able to say which one returns a number and which returns a row's attribute, and write FIRST_VALUE with an ORDER BY that differs from the column being returned.
Explain argmin versus min, why MIN(product) answers a different question, and how the frame affects FIRST_VALUE but not an unordered aggregate window.
Bring up determinism unprompted: ties in the ordering key, NULL placement in the sort, and why reversing the ordering beats LAST_VALUE for the far end of the window.
Judge when the extreme row's attributes belong in the query at all versus being denormalised or precomputed, and set the team's convention for total orderings so reports do not silently change row selection.
## Two different questions "What is the lowest price in this category?" and "Which product has the lowest price in this category?" are different questions, and SQL answers them with different tools. - `MIN(price) OVER (PARTITION BY category)` answers the first. It reduces the `price` column over the rows in scope and returns the minimum. The only thing it can return is a value of its own argument. - `FIRST_VALUE(product) OVER (PARTITION BY category ORDER BY price)` answers the second. It identifies a *row* by the window ordering and then returns that row's `product` column. The decoupling of the ordering key from the returned expression is what makes value functions worth having. Relational algebra people call the second form an *argmin*: not the extreme value, but the row that attains it. ## A worked example ```sql SELECT category, product, price, MIN(price) OVER (PARTITION BY category) AS cheapest_price, FIRST_VALUE(product) OVER (PARTITION BY category ORDER BY price) AS cheapest_product FROM products; ``` Every row of a category shows the same two values: the numeric floor, and the name of the product sitting on it. You could not obtain `cheapest_product` by adding `product` to a `MIN` call — `MIN(product)` would return the alphabetically smallest product name, which is a different row entirely and a classic wrong answer. ## Frame sensitivity `FIRST_VALUE`, `LAST_VALUE` and `NTH_VALUE` read positions inside the *frame*. With `ORDER BY` present and no explicit frame, the default frame runs from the start of the partition to the current row, which is harmless for `FIRST_VALUE` — the partition's first row is always inside it — but decisive if you narrow the frame: ```sql FIRST_VALUE(price) OVER (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ``` returns the price from two rows back, or from the partition's first row when fewer than two precede. `MIN(price) OVER (PARTITION BY category)` with no `ORDER BY` has the whole partition as its frame and is unaffected by such concerns — but add `ORDER BY ts` to that same aggregate and it silently becomes a *running* minimum, which is a different metric again. ## Ties If two products share the lowest price, the window ordering does not distinguish them and `FIRST_VALUE` may return either. This is nondeterminism, not randomness: the same query may return different answers across engines, plans, or data reorganisations. Fix it by making the ordering total: ```sql FIRST_VALUE(product) OVER (PARTITION BY category ORDER BY price, product_id) ``` The same discipline applies to `LAST_VALUE` and `NTH_VALUE`. ## NULLs in the ordering key Where NULLs sort by default is not uniform across engines, so a NULL `price` can end up first and make `FIRST_VALUE(product)` return the row with no price at all. Be explicit — add `NULLS LAST` where your engine supports it, or exclude the NULL rows in a `WHERE` clause before the window runs. ## Getting the other end The most expensive product is *not* `LAST_VALUE(product) OVER (PARTITION BY category ORDER BY price)` unless you also widen the frame, because the default frame stops at the current row. The safer idiom is to reverse the ordering: `FIRST_VALUE(product) OVER (PARTITION BY category ORDER BY price DESC)`. `NTH_VALUE(product, 2)` fetches the second-placed row, again subject to the frame containing at least that many rows. ## When an aggregate is still the right tool If you only need the value and not the row, `MIN`/`MAX` is simpler, needs no ordering, and expresses the intent directly. Reach for `FIRST_VALUE` when the answer is a *different attribute* of the extreme row — the product name, the customer id, the timestamp of the first event — or when you need it attached to every detail row rather than collapsed. ## What interviewers are checking That you can articulate value-of-a-column versus attribute-of-a-row, that you do not reach for `MIN(product)` when asked which product, that you know value functions obey the frame while an unordered aggregate window does not, and that you volunteer the tiebreaker without being prompted.
- Why is MIN(product) the wrong way to get the cheapest product's name?`MIN(product)` applies the minimum to the product column itself, returning the alphabetically or numerically smallest product name in the category. That row has nothing to do with the lowest price. The comparison must run on `price` while the returned expression is `product`, which only a value function such as `FIRST_VALUE` can express.
- How do you make FIRST_VALUE deterministic when two rows tie on the ordering key?Extend the window `ORDER BY` until it is total — add a unique or near-unique tiebreaker such as the primary key: `ORDER BY price, product_id`. Without it the engine may return either tied row, and the choice can change between plans or after data reorganisation even though the data has not changed.
MIN reads the lowest number off a price list. FIRST_VALUE sorts the list and then reads whatever you point at on the top line — the name, the SKU, the supplier.
saying these in an interview costs you the question
- Uses MIN on the name column to find the cheapest item
- Thinks FIRST_VALUE can only return the ordering column
- Assumes ties give a stable, repeatable row
- Forgets FIRST_VALUE reads the frame, not the partition
- Uses LAST_VALUE for the maximum without widening the frame