Top N Per Group

“The top 3 products per category”, “the most recent order per customer”, “the highest-paid employee per department” — one pattern, asked constantly. Four solutions, and the tie question decides between them.

Why GROUP BY does not answer it

SELECT customer_id, max(amount) AS biggest
FROM orders WHERE status = 'shipped'
GROUP BY customer_id ORDER BY customer_id;

┌─────────────┬─────────┐
│ customer_id │ biggest │
├─────────────┼─────────┤
│           1 │  200.00 │
│           2 │  310.00 │
│           4 │  150.00 │
│        NULL │   75.00 │
└─────────────┴─────────┘

That is the amount, and the question asked for the order. Adding order_id fails:

SELECT customer_id, order_id, max(amount) FROM orders GROUP BY customer_id;

Binder Error: column "order_id" must appear in the GROUP BY clause or must be part of an
aggregate function.

MAX returns a value, not a row. That sentence is the whole reason this pattern exists.

Solution 1: ROW_NUMBER in a CTE — the default answer

WITH ranked AS (
    SELECT o.order_id, c.name, o.order_date, o.amount,
           row_number() OVER (PARTITION BY o.customer_id
                              ORDER BY o.amount DESC, o.order_id) AS rn
    FROM orders o JOIN customers c USING (customer_id)
    WHERE o.status = 'shipped'
)
SELECT name, order_id, order_date, amount
FROM ranked WHERE rn = 1
ORDER BY amount DESC;

┌─────────┬──────────┬────────────┬────────┐
│  name   │ order_id │ order_date │ amount │
├─────────┼──────────┼────────────┼────────┤
│ Bo      │      103 │ 2024-03-18 │ 310.00 │
│ Ana     │      105 │ 2024-04-20 │ 200.00 │
│ Di      │      107 │ 2024-05-30 │ 150.00 │
└─────────┴──────────┴────────────┴────────┘

Three things to point at while writing it:

• PARTITION BY o.customer_id restarts the numbering per customer. Forgetting it ranks globally and returns one row overall.
• , o.order_id in the ORDER BY is the tiebreaker. Without it, two orders of equal amount produce a non-deterministic winner and the query can return different rows on different runs.
• WHERE o.status = 'shipped' inside the CTE, not outside. Filtering after ranking would rank cancelled orders first and then delete them, leaving customers with no row at all.

Change rn = 1 to rn <= 3 for top 3. Nothing else moves — which is why this is the version to default to.

Ties change the answer

WITH sales AS (
    SELECT * FROM (VALUES
        ('north', 'Ana', 500), ('north', 'Bo', 500), ('north', 'Cy', 300),
        ('south', 'Di', 400),  ('south', 'Eve', 200)) AS t(region, rep, amount)
)
SELECT region, rep, amount,
       row_number() OVER (PARTITION BY region ORDER BY amount DESC) AS rn,
       rank()       OVER (PARTITION BY region ORDER BY amount DESC) AS rnk,
       dense_rank() OVER (PARTITION BY region ORDER BY amount DESC) AS dr
FROM sales ORDER BY region, amount DESC, rep;

┌─────────┬─────────┬────────┬───────┬───────┬───────┐
│ region  │   rep   │ amount │  rn   │  rnk  │  dr   │
├─────────┼─────────┼────────┼───────┼───────┼───────┤
│ north   │ Ana     │    500 │     1 │     1 │     1 │
│ north   │ Bo      │    500 │     2 │     1 │     1 │
│ north   │ Cy      │    300 │     3 │     3 │     2 │
│ south   │ Di      │    400 │     1 │     1 │     1 │
│ south   │ Eve     │    200 │     2 │     2 │     2 │
└─────────┴─────────┴────────┴───────┴───────┴───────┘

“The top rep per region” with rn = 1 returns Ana and drops Bo — who sold exactly the same amount. With rnk = 1 both appear.

rn = 1   → 2 rows (Ana, Di)        one winner per region, arbitrary on ties
rnk = 1  → 3 rows (Ana, Bo, Di)    all tied winners

“Ana and Bo both sold 500. Do you want exactly one row per region, or everyone tied at the top? ROW_NUMBER gives one and picks arbitrarily; RANK gives both. I’ll use RANK unless you need exactly one.”

Asking this is the difference between a correct query and the right query. Interviewers frequently plant the tie on purpose.

Solution 2: correlated subquery — portable, and slow

SELECT c.name, o.order_id, o.amount
FROM orders o JOIN customers c USING (customer_id)
WHERE o.status = 'shipped'
  AND o.amount = (SELECT max(o2.amount) FROM orders o2
                  WHERE o2.customer_id = o.customer_id AND o2.status = 'shipped')
ORDER BY o.amount DESC;

┌─────────┬──────────┬────────┐
│  name   │ order_id │ amount │
├─────────┼──────────┼────────┤
│ Bo      │      103 │ 310.00 │
│ Ana     │      105 │ 200.00 │
│ Di      │      107 │ 150.00 │
└─────────┴──────────┴────────┘

Same answer, and it works on engines without window functions (MySQL 5.7, SQLite before 3.25).

Two costs to name:

• It re-runs the subquery per row unless the planner rewrites it. Modern PostgreSQL and DuckDB often do; MySQL 5.7 often does not.
• It returns all ties, like RANK. That may be what you want, but it is implicit rather than chosen.

Extending it to top-3 requires a counting subquery, and readability collapses:

SELECT * FROM orders o
WHERE (SELECT count(*) FROM orders o2
       WHERE o2.customer_id = o.customer_id AND o2.amount > o.amount) < 3;

That is the honest reason the window version is the default: it scales to top-N by changing one number.

Solution 3: LATERAL — stop after N

SELECT c.name, top_orders.order_id, top_orders.amount
FROM customers c,
     LATERAL (SELECT o.order_id, o.amount
              FROM orders o
              WHERE o.customer_id = c.customer_id AND o.status = 'shipped'
              ORDER BY o.amount DESC, o.order_id
              LIMIT 2) AS top_orders
ORDER BY c.name, top_orders.amount DESC;

┌─────────┬──────────┬────────┐
│  name   │ order_id │ amount │
├─────────┼──────────┼────────┤
│ Ana     │      105 │ 200.00 │
│ Ana     │      101 │ 120.00 │
│ Bo      │      103 │ 310.00 │
│ Di      │      107 │ 150.00 │
└─────────┴──────────┴────────┘

The subquery runs once per outer row and can reference c.customer_id — that back-reference is what LATERAL enables and a plain subquery in FROM cannot do.

The performance argument is real: with an index on orders(customer_id, amount DESC), each LATERAL execution reads two index entries and stops. The window version ranks every order for every customer first. When the outer table is small and the inner one is huge, that difference is large.

CROSS JOIN LATERAL is the explicit spelling; SQL Server calls it CROSS APPLY. Note that Cy is absent because her only order is cancelled — LEFT JOIN LATERAL ... ON true keeps her with NULLs.

Solution 4: DISTINCT ON — shortest, PostgreSQL and DuckDB only

SELECT DISTINCT ON (o.customer_id) c.name, o.order_id, o.amount
FROM orders o JOIN customers c USING (customer_id)
WHERE o.status = 'shipped'
ORDER BY o.customer_id, o.amount DESC, o.order_id;

┌─────────┬──────────┬────────┐
│  name   │ order_id │ amount │
├─────────┼──────────┼────────┤
│ Ana     │      105 │ 200.00 │
│ Bo      │      103 │ 310.00 │
│ Di      │      107 │ 150.00 │
└─────────┴──────────┴────────┘

Four lines. The rule: DISTINCT ON (expr) keeps the first row for each distinct value of expr, and “first” is decided by the ORDER BY — which must therefore start with the same expression.

It only does top-1. It does not exist in MySQL, SQL Server, or SQLite. Use it and say so:

“DISTINCT ON is a PostgreSQL extension — DuckDB supports it too. If we need portability I’d use the ROW_NUMBER version, which is the same idea in standard SQL.”

Naming a dialect feature as a dialect feature reads as experience. Using one silently and being caught reads as the opposite.

All four, side by side

APPROACH             TOP-N?   TIES        PORTABLE          WHEN
row_number CTE       any N    one, arb.   any window SQL    the default
rank CTE             any N    all tied    any window SQL    ties must all appear
correlated subquery  1 easily all tied    everywhere        legacy MySQL / SQLite
LATERAL              any N    by LIMIT    PG, MySQL 8, MSSQL small outer, indexed inner
DISTINCT ON          1 only   one, arb.   PostgreSQL, DuckDB shortest, top-1 only

The variants that use the same shape

-- most RECENT order per customer: change the ORDER BY, nothing else
WITH ranked AS (
    SELECT c.name, o.order_id, o.order_date,
           row_number() OVER (PARTITION BY o.customer_id
                              ORDER BY o.order_date DESC, o.order_id DESC) AS rn
    FROM orders o JOIN customers c USING (customer_id)
)
SELECT name, order_id, order_date FROM ranked WHERE rn = 1 ORDER BY order_date DESC;

┌─────────┬──────────┬────────────┐
│  name   │ order_id │ order_date │
├─────────┼──────────┼────────────┤
│ Di      │      107 │ 2024-05-30 │
│ Bo      │      106 │ 2024-05-05 │
│ Ana     │      105 │ 2024-04-20 │
│ Cy      │      104 │ 2024-04-02 │
└─────────┴──────────┴────────────┘

Same query, different ORDER BY inside the window. So are: the second-highest (rn = 2), the median-ish (ntile(2)), the first purchase per customer, and the latest status per entity.

Recognising that “most recent X per Y” and “top X per Y” are the same query is worth more than memorising either.

Practice

You get the amount. Adding order_id is a Binder Error.

rn = 1  → Ana only        rnk = 1 → Ana and Bo

Customers whose top order was cancelled return no row at all.

Syntax error — DISTINCT ON is PostgreSQL and DuckDB only.

Next: CTEs and subqueries — readability, recursion, and the correlated subquery that runs once per row.