Top N Per Group

Concept

The most recent order per customer. The highest paid employee per department. The best selling product per category. All the same shape: rank rows inside a group, keep the top N. It is the single most common non trivial SQL question there is.

Method 1: ROW_NUMBER (preferred)

WITH ranked AS (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, id DESC) AS rn
    FROM   orders o
)
SELECT customer_id, id AS order_id, order_date, total
FROM   ranked
WHERE  rn = 1
ORDER BY customer_id;

To get the top three instead of the top one, change rn = 1 to rn <= 3. Nothing else changes, which is why this method scales to any N.

Method 2: correlated subquery

SELECT o.customer_id, o.id AS order_id, o.order_date, o.total
FROM   orders o
WHERE  o.order_date = (SELECT MAX(o2.order_date)
                       FROM   orders o2
                       WHERE  o2.customer_id = o.customer_id)
ORDER BY o.customer_id;

Works everywhere, including MySQL 5.7. The catch: if a customer has two orders on the same date, both come back. That is either a bug or the requirement, so decide deliberately.

Method 3: pre aggregate and join

SELECT o.customer_id, o.id AS order_id, o.order_date, o.total
FROM   orders o
JOIN  (SELECT customer_id, MAX(order_date) AS latest
       FROM   orders
       GROUP BY customer_id) m
       ON m.customer_id = o.customer_id AND m.latest = o.order_date
ORDER BY o.customer_id;

Same tie behaviour as method 2, but the aggregate runs once rather than once per row, which usually makes it the fastest option on older engines.

Method 4: LATERAL / CROSS APPLY

-- PostgreSQL, MySQL 8.0.14+, Oracle 12c+
SELECT c.name, o.id AS order_id, o.order_date, o.total
FROM   customers c
LEFT JOIN LATERAL (SELECT id, order_date, total
                   FROM   orders
                   WHERE  customer_id = c.id
                   ORDER BY order_date DESC, id DESC
                   LIMIT  1) o ON TRUE;

-- SQL Server spells it CROSS APPLY / OUTER APPLY
-- OUTER APPLY (SELECT TOP 1 ... ORDER BY ...) o

A lateral join lets the subquery reference the outer row, so it can carry its own ORDER BY and LIMIT. It is often the fastest method when the group count is small and each group is large, because the index gives the top row immediately. MariaDB does not support LATERAL.

Comparison

Important rules

• Add a unique tie breaker to the window ORDER BY, or the "top" row is not deterministic.
• ROW_NUMBER gives exactly one row per group; RANK gives all tied rows.
• Rows filtered out by WHERE are never ranked - filter before, not after.
• An index on (partition_column, order_column DESC) is what makes any of these fast.

Common mistakes

• Writing SELECT customer_id, MAX(order_date), total FROM orders GROUP BY customer_id - the total comes from an arbitrary row, not from the latest order. This is the classic wrong answer.
• Omitting the tie breaker and getting different results on different runs.
• Using RANK where exactly one row per group was required.
• Filtering on rn in the same query level that computes it.

Best practices

• Default to ROW_NUMBER in a CTE - it reads clearly and generalises to any N.
• Always end the window ORDER BY with the primary key.
• Create a composite index matching the partition and order columns.
• Decide explicitly whether ties should all appear, and choose the function accordingly.

Practice

1. Return the two highest paid employees in each department.
2. Return each customer's single largest order, breaking ties by order id.
3. Explain precisely why the GROUP BY with MAX(order_date) and a bare total column is wrong.