SQL: Find Rows with the Max Value per Group (3 Clean Solutions)

The Classic Interview Question

"Return the full order row for the most expensive purchase per customer" — this shows up in every SQL interview and in real dashboards daily. The naive answer, GROUP BY customer_id with MAX(amount), gives you the amount but not the rest of the row. Here are the three solutions I actually use, in order of preference.

Solution 1: ROW_NUMBER() Window Function (Best)

SELECT customer_id, amount, order_date, status FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn FROM orders) t WHERE rn = 1;

Reads cleanly, handles ties deterministically (only one row per group), and the subquery is easy to extend — change ORDER BY to get the latest order instead of the biggest. This is the answer to use in MySQL 8+ and PostgreSQL.

Solution 2: NOT EXISTS (Works Everywhere)

SELECT o.* FROM orders o WHERE NOT EXISTS (SELECT 1 FROM orders o2 WHERE o2.customer_id = o.customer_id AND o2.amount > o.amount);

Semantically "no other row in the same group has a bigger amount". Works on older MySQL 5.x, and it naturally returns all tied rows — if two orders share the same max amount, both come back.

Solution 3: Self-Join + MAX Subquery

SELECT o.* FROM orders o JOIN (SELECT customer_id, MAX(amount) AS max_amount FROM orders GROUP BY customer_id) m ON m.customer_id = o.customer_id AND m.max_amount = o.amount;

This is the pattern people write first because it follows the GROUP BY instinct. It works, but on a big orders table you scan twice. For a few thousand rows nobody notices; past a million, the window function wins.

Which One Should You Use?

My rule of thumb: ROW_NUMBER() unless I specifically need ties (then NOT EXISTS). The self-join is fine as a teaching example but I wouldn't put it in production on a hot table. On PostgreSQL you can also use DISTINCT ON (customer_id) with ORDER BY amount DESC — concise, but it's a Postgres-only extension.

Try It Yourself

Describe your table to our free SQL generator — say "most expensive purchase per customer with the full row" and it'll write the window function version for your dialect.