{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"the-row-behind-the-max","title":"The row behind the MAX: which order was the biggest","url":"https://addaly.com/learn/data-and-sql/the-row-behind-the-max","position":30,"of":66,"minutes":9,"answer":"MAX returns a value with no memory of its row; to get the row, join back to the summary, use DISTINCT ON or arg_max.","keyIdea":"MAX returns a value with no memory of its row; to get the row, join back to the summary, use DISTINCT ON or arg_max, and compare output rows against group count to catch ties and all-NULL groups."},"body":{"format":"markdown","text":"## MAX gives you a value, not a row\n\n\"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:\n\n```sql\nSELECT customer_id, max(amount), order_id, placed_at\nFROM orders\nGROUP BY customer_id;\n-- ERROR: column \"orders.order_id\" must appear in the GROUP BY clause\n```\n\nThe 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\".\n\n## Join back to the summary\n\nThe portable approach in two steps: compute the maxima, then join them back to find the rows that hit them.\n\n```sql\nWITH peak AS (\n  SELECT customer_id, max(amount) AS max_amount\n  FROM orders GROUP BY customer_id\n)\nSELECT o.customer_id, o.order_id, o.placed_at, o.amount\nFROM orders o\nJOIN peak p ON p.customer_id = o.customer_id AND p.max_amount = o.amount;\n```\n\nIt 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.\n\n## Pick one row per group directly\n\nPostgres has `DISTINCT ON`, which is the clearest answer to \"one row per group, chosen by this ordering\":\n\n```sql\nSELECT DISTINCT ON (customer_id)\n       customer_id, order_id, placed_at, amount\nFROM orders\nORDER BY customer_id, amount DESC, placed_at ASC;\n```\n\nRead 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.\n\nDuckDB 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.\n\n## The correlated form\n\nThe version that reads most like the question is a subquery that fetches the top row per customer:\n\n```sql\nSELECT c.id,\n       (SELECT order_id FROM orders o\n        WHERE o.customer_id = c.id\n        ORDER BY amount DESC, placed_at LIMIT 1) AS biggest_order\nFROM customers c;\n```\n\nIt 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.\n\n## Which to use\n\nWhen 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.\n\nWhichever 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.\n\n## The check that catches the wrong one\n\nWhatever 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."},"check":{"question":"Using the join-back pattern (a CTE of `max(amount)` per customer joined to `orders` on customer and amount), a report of \"each customer's biggest order\" returns 1,046 rows for 1,000 customers. What explains the extra rows?","options":[{"text":"The join condition is missing the customer, so amounts are matching across customers.","correct":false,"tempting":"Joining on amount alone would multiply rows enormously across a thousand customers, not add 4.6%."},{"text":"`max()` returned NULL for customers with no orders in the period, and NULL matched every row of `orders` in the join, adding rows for them.","correct":false,"tempting":"NULL never matches anything in a join condition; customers with no orders produce zero rows, not extra ones."},{"text":"Forty-six customers have two or more orders that tie at their maximum amount, and the join returns every tied row.","correct":true},{"text":"The CTE was materialised and read twice, once for each side of the join, duplicating some rows.","correct":false,"tempting":"Materialisation changes how a CTE is executed, never how many rows a join emits."}]},"prev":{"slug":"string-aggregation-and-arrays","title":"Listing the members of a pile: string_agg and array_agg","url":"https://addaly.com/learn/data-and-sql/string-aggregation-and-arrays"},"next":{"slug":"rollups-and-subtotals","title":"Subtotals and grand totals in one query: ROLLUP and GROUPING","url":"https://addaly.com/learn/data-and-sql/rollups-and-subtotals"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'The row behind the MAX: which order was the biggest', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/the-row-behind-the-max (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"The row behind the MAX: which order was the biggest","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":3,"slug":"summaries-and-the-piles-behind-them","title":"Summaries, and the piles behind them"},"answer":null,"keyIdea":"MAX returns a value with no memory of its row; to get the row, join back to the summary, use DISTINCT ON or arg_max, and compare output rows against group count to catch ties and all-NULL groups.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"max-gives-you-a-value-not-a-row","runs":[{"text":"MAX gives you a value, not a row"}]},{"t":"p","runs":[{"text":"“What was each customer’s largest order?” is easy: "},{"text":"max(amount)","code":true},{"text":" grouped by customer. “Which order was it, and when?” is the question people actually ask, and the obvious query does not work:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT customer_id, max(amount), order_id, placed_at\nFROM orders\nGROUP BY customer_id;\n-- ERROR: column \"orders.order_id\" must appear in the GROUP BY clause"},{"t":"p","runs":[{"text":"The database is right to refuse. "},{"text":"max(amount)","code":true},{"text":" squeezes the pile to one number; "},{"text":"order_id","code":true},{"text":" 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”."}]},{"t":"h2","id":"join-back-to-the-summary","runs":[{"text":"Join back to the summary"}]},{"t":"p","runs":[{"text":"The portable approach in two steps: compute the maxima, then join them back to find the rows that hit them."}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH peak AS (\n  SELECT customer_id, max(amount) AS max_amount\n  FROM orders GROUP BY customer_id\n)\nSELECT o.customer_id, o.order_id, o.placed_at, o.amount\nFROM orders o\nJOIN peak p ON p.customer_id = o.customer_id AND p.max_amount = o.amount;"},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"pick-one-row-per-group-directly","runs":[{"text":"Pick one row per group directly"}]},{"t":"p","runs":[{"text":"Postgres has "},{"text":"DISTINCT ON","code":true},{"text":", which is the clearest answer to “one row per group, chosen by this ordering”:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT DISTINCT ON (customer_id)\n       customer_id, order_id, placed_at, amount\nFROM orders\nORDER BY customer_id, amount DESC, placed_at ASC;"},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"DuckDB and ClickHouse have "},{"text":"arg_max(order_id, amount)","code":true},{"text":", which returns the "},{"text":"order_id","code":true},{"text":" from the row where "},{"text":"amount","code":true},{"text":" is largest, as an ordinary aggregate you can put next to "},{"text":"count(*)","code":true},{"text":". BigQuery gets there with "},{"text":"ARRAY_AGG(order_id ORDER BY amount DESC LIMIT 1)[OFFSET(0)]","code":true},{"text":". Each is the same idea: an aggregate that remembers which row won."}]},{"t":"h2","id":"the-correlated-form","runs":[{"text":"The correlated form"}]},{"t":"p","runs":[{"text":"The version that reads most like the question is a subquery that fetches the top row per customer:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT c.id,\n       (SELECT order_id FROM orders o\n        WHERE o.customer_id = c.id\n        ORDER BY amount DESC, placed_at LIMIT 1) AS biggest_order\nFROM customers c;"},{"t":"p","runs":[{"text":"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 "},{"text":"LATERAL","code":true},{"text":" 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."}]},{"t":"h2","id":"which-to-use","runs":[{"text":"Which to use"}]},{"t":"p","runs":[{"text":"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 "},{"text":"DISTINCT ON","code":true},{"text":" in Postgres, "},{"text":"arg_max","code":true},{"text":" 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 "},{"text":"row_number()","code":true},{"text":" is the general tool that makes the top-N question a two-line filter."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-check-that-catches-the-wrong-one","runs":[{"text":"The check that catches the wrong one"}]},{"t":"p","runs":[{"text":"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 "},{"text":"max()","code":true},{"text":" ignores, so they had no maximum and no row. Both are findings; neither should be a surprise you discover in a meeting."}]}],"check":{"question":[{"text":"Using the join-back pattern (a CTE of "},{"text":"max(amount)","code":true},{"text":" per customer joined to "},{"text":"orders","code":true},{"text":" on customer and amount), a report of “each customer’s biggest order” returns 1,046 rows for 1,000 customers. What explains the extra rows?"}],"options":[{"text":[{"text":"The join condition is missing the customer, so amounts are matching across customers."}],"correct":false,"tempting":[{"text":"Joining on amount alone would multiply rows enormously across a thousand customers, not add 4.6%."}]},{"text":[{"text":"max()","code":true},{"text":" returned NULL for customers with no orders in the period, and NULL matched every row of "},{"text":"orders","code":true},{"text":" in the join, adding rows for them."}],"correct":false,"tempting":[{"text":"NULL never matches anything in a join condition; customers with no orders produce zero rows, not extra ones."}]},{"text":[{"text":"Forty-six customers have two or more orders that tie at their maximum amount, and the join returns every tied row."}],"correct":true,"tempting":null},{"text":[{"text":"The CTE was materialised and read twice, once for each side of the join, duplicating some rows."}],"correct":false,"tempting":[{"text":"Materialisation changes how a CTE is executed, never how many rows a join emits."}]}],"order":[0,2,1,3],"why":null},"prev":{"slug":"string-aggregation-and-arrays","title":"Listing the members of a pile: string_agg and array_agg","minutes":7},"next":{"slug":"rollups-and-subtotals","title":"Subtotals and grand totals in one query: ROLLUP and GROUPING","minutes":8}}}