The row behind the MAX: which order was the biggest

MAX gives you a value, not a row

“What was each customer’s largest order?” is easy: max(amount) grouped by customer. “Which order was it, and when?” is the question people actually ask, and the obvious query does not work:

SELECT customer_id, max(amount), order_id, placed_at
FROM orders
GROUP BY customer_id;
-- ERROR: column "orders.order_id" must appear in the GROUP BY clause

The database is right to refuse. max(amount) squeezes the pile to one number; order_id has four values in that pile and no rule says which one to show. MySQL in a permissive mode will pick one at random and make you think it worked, which is the worst outcome. You need a way to say “the row where the amount is the max”.

Join back to the summary

The portable approach in two steps: compute the maxima, then join them back to find the rows that hit them.

WITH peak AS (
  SELECT customer_id, max(amount) AS max_amount
  FROM orders GROUP BY customer_id
)
SELECT o.customer_id, o.order_id, o.placed_at, o.amount
FROM orders o
JOIN peak p ON p.customer_id = o.customer_id AND p.max_amount = o.amount;

It works everywhere, including SQLite and older MySQL. It has one honest flaw: ties. A customer with two orders of exactly ₹5,000 gets two rows, and a report that expected one row per customer now has more rows than customers. Sometimes that is the truth you want. When it is not, you need a tiebreaker, and a join cannot express “the earliest of the tied ones” without another round of the same trick.

Pick one row per group directly

Postgres has DISTINCT ON, which is the clearest answer to “one row per group, chosen by this ordering”:

SELECT DISTINCT ON (customer_id)
       customer_id, order_id, placed_at, amount
FROM orders
ORDER BY customer_id, amount DESC, placed_at ASC;

Read it as: sort within each customer by amount descending, then by date, and keep the first row of each customer. The tiebreaker is just the next sort key. It is fast, it returns exactly one row per group, and it is a Postgres extension that no other engine spells the same way.

DuckDB and ClickHouse have arg_max(order_id, amount), which returns the order_id from the row where amount is largest, as an ordinary aggregate you can put next to count(*). BigQuery gets there with ARRAY_AGG(order_id ORDER BY amount DESC LIMIT 1)[OFFSET(0)]. Each is the same idea: an aggregate that remembers which row won.

The correlated form

The version that reads most like the question is a subquery that fetches the top row per customer:

SELECT c.id,
       (SELECT order_id FROM orders o
        WHERE o.customer_id = c.id
        ORDER BY amount DESC, placed_at LIMIT 1) AS biggest_order
FROM customers c;

It returns one column, so a second column means a second subquery, and it runs once per customer unless the planner is clever. Postgres’s LATERAL join lets the subquery return several columns at once and is the tool when you need the whole row, but the mechanism is the same.

Which to use

When the tie is meaningful and you want to see both rows, join back to the summary. When you want exactly one row and have a tiebreaker, use DISTINCT ON in Postgres, arg_max in DuckDB, or the array trick in BigQuery. When you need the top three per customer rather than the top one, none of these is comfortable, and the next module’s row_number() is the general tool that makes the top-N question a two-line filter.

Whichever form you pick, put a comment above the query naming the tiebreaker. “Latest wins” and “earliest wins” are both defensible, and the next reader cannot tell from the SQL alone which one you chose or whether you chose at all.

The check that catches the wrong one

Whatever form you choose, count the output rows and compare them with the number of groups. More rows than groups means ties leaked through. Fewer means some groups had only NULL amounts, which max() ignores, so they had no maximum and no row. Both are findings; neither should be a surprise you discover in a meeting.