SQL Top N Per Group: 3 Methods That Scale in Production

'Top 3 products per category.' 'Latest 2 orders per customer.' 'Highest-paid 5 engineers per department.' You've written GROUP BY a hundred times, but top-N per group isn't aggregation — it's ranking inside partitions. And if your first instinct is GROUP BY + MAX + self-join, you'll fetch the right score with the wrong row's name on it. Don't ship that.

Three methods compete: ROW_NUMBER (modern, one pass), correlated subquery (portable to ancient MySQL), and LATERAL (Postgres797 power tool). Each wins somewhere and embarrasses you elsewhere. Pick wrong and your 2M-row table sorts on disk for 40 seconds.

This guide runs all three on the same dataset with copy-paste SQL you can execute today. You'll see tie semantics done right, the composite index that makes windows fly, and the version matrix that stops deploys from exploding on legacy replicas.

The Problem and the Shared Fixture

The running example: products(id, category, name, price), goal = top 2 per category by price. Note books has a tie at 49.99 (ids 2,3) — every method's tie behavior will show on this fixture. Run the CREATE + INSERT once; all queries below execute against it unchanged.

Why fixtures matter: tie behavior is invisible on distinct scores. This 8-row table exposes exact-N vs with-ties on the first run — the decision stakeholders must make. Keep it; interviewers love asking 'what happens on your data when two tie?' and here you can show them.

Expected exact-N answer (price DESC, id tiebreak): books → ids 2,3; games → ids 5,6. Expected with-ties at N=2 (RANK): identical here — the divergence needs a 3-way tie, covered in section 5.

-- Same fixture for every method below. Run it once:
CREATE TABLE products (
  id INT PRIMARY KEY,
  category VARCHAR(40) NOT NULL,
  name VARCHAR(80) NOT NULL,
  price NUMERIC(10,2) NOT NULL
};
INSERT INTO products (id, category, name, price) VALUES
  (1,'books','SQL Guide',29.99),(2,'books','DB Internals',49.99),
  (3,'books','Query Tuning',49.99),(4,'books','Old Almanac',9.99),
  (5,'games','Rogue Quest',59.99),(6,'games','Pixel Racer',39.99),
  (7,'games','Chess Master',39.99),(8,'games','Retro Pack',19.99);
-- Goal used throughout: top 2 products per category by price.

Method 1 — ROW_NUMBER (The Modern Default)

ROW_NUMBER partitions rows by category, orders each partition by price DESC, and numbers 1..N. The outer query keeps rn <= 2. One table pass plus a per-partition sort — O(P log P) per partition with P = partition size.

The id ASC tiebreak is not cosmetic: without a unique ordering key, tied rows (ids 2,3) can swap between runs and replicas. Deterministic order is a correctness property for pagination, tests, and report diffs.

Variants in one line: swap ROW_NUMBER → RANK for with-ties (both 49.99 books share rn=1, next is 3), → DENSE_RANK for tier numbers without gaps. Same query shape, three business meanings — comment your choice.

-- Method 1: ROW_NUMBER (MySQL 8+, Postgres, SQL Server, Oracle, SQLite 3.25+)
WITH ranked AS (
  SELECT id, category, name, price,
         ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY price DESC, id ASC   -- id tiebreak = deterministic
         ) AS rn
  FROM products
)
SELECT id, category, name, price
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;
-- books -> (2, DB Internals, 49.99), (3, Query Tuning, 49.99)
-- games -> (5, Rogue Quest, 59.99), (6, Pixel Racer, 39.99)

Method 2 — Correlated Subquery (The Portable Fallback)

Logic: keep rows with fewer than N strictly-better rows in the same group. The (price > OR price = AND id <) tuple comparison encodes exact-N determinism identical to ROW_NUMBER's tiebreak; dropping the id clause yields with-ties semantics.

Cost: the subquery re-executes per outer row — O(R × P) with R rows and P partition size. On 10^4 rows it's fine; past 10^5 it degrades visibly; at 10^6+ it's a pager incident. An index on (category, price DESC, id) converts each execution to an index count, buying roughly 10x but not changing the complexity class.

Use when the database lacks windows (MySQL 5.7, ancient SQLite) or when policy forbids CTEs. Otherwise prefer Method 1 and say why.

-- Method 2: correlated COUNT subquery (works on MySQL 5.7, any SQL)
SELECT p1.id, p1.category, p1.name, p1.price
FROM products p1
WHERE (
  SELECT COUNT(*)
  FROM products p2
  WHERE p2.category = p1.category
    AND (p2.price > p1.price
         OR (p2.price = p1.price AND p2.id < p1.id))
) < 2
ORDER BY p1.category, p1.price DESC, p1.id;
-- Same result as ROW_NUMBER above. COUNT(*) counts strictly-better rows;
-- fewer than 2 better rows  <=>  row is in the top 2 (exact-N semantics).
-- Drop the id tiebreak line for RANK-like with-ties behavior.

Method 3 — LATERAL / CROSS APPLY (The Indexed Fast Path)

LATERAL runs the subquery once per group with LIMIT 2 pushed inside — each execution is an index range scan returning 2 rows, not a global sort. With G groups the cost is G × O(log P) instead of O(R log P). For 10^4 groups × small N, this beats ROW_NUMBER by 5-50x.

Requirements: composite index (category, price DESC, id) so each lateral leg is a pure index walk. Without it, LATERAL degrades to G sequential scans — worse than the window. EXPLAIN should show 'Index Scan using ... Limit' per leg.

MySQL has no LATERAL (until 8.0.14's limited form); SQL Server spells it CROSS APPLY with TOP. Know both spellings — cross-dialect fluency is a senior signal in polyglot shops.

-- Method 3a: LATERAL (Postgres 9.3+)
SELECT g.category, t.id, t.name, t.price
FROM (SELECT DISTINCT category FROM products) g
CROSS JOIN LATERAL (
  SELECT id, name, price
  FROM products p
  WHERE p.category = g.category
  ORDER BY p.price DESC, p.id ASC
  LIMIT 2
) t
ORDER BY g.category, t.price DESC;

-- Method 3b: CROSS APPLY (SQL Server) — same shape, different keyword:
-- SELECT g.category, t.id, t.name, t.price
-- FROM (SELECT DISTINCT category FROM products) g
-- CROSS APPLY (
--   SELECT TOP (2) id, name, price FROM products p
--   WHERE p.category = g.category ORDER BY p.price DESC, p.id ASC
-- ) t;

Ties and the MAX-Join Anti-Pattern

The MAX-join answers 'what is the top score' then glues arbitrary rows onto it. With ties it multiplies rows (both 49.99 books match); with strict SQL modes it errors on unaggregated columns; without them it silently picks indeterminate names — the 6-week incident above.

Ties done right: exact-N → ROW_NUMBER + id tiebreak (documented). With-ties → RANK (all tied-for-Nth included, counts vary). Tiers → DENSE_RANK (distinct scores numbered). Put the business choice in a comment; future editors must see intent.

Test ties explicitly: add a third 49.99 book and confirm ROW_NUMBER still returns 2 rows while RANK returns 3. That one INSERT is the tie contract made executable.

-- ANTI-PATTERN (do not ship): GROUP BY + MAX join
-- Returns right prices, indeterminate names:
SELECT p.category, p.name, m.max_price
FROM products p
JOIN (SELECT category, MAX(price) AS max_price
      FROM products GROUP BY category) m
  ON m.category = p.category AND m.max_price >= p.price
-- With the tie (ids 2,3 both 49.99) this returns 3+ rows for books
-- and MySQL's ONLY_FULL_GROUP_BY rejects the unaggregated p.name.
-- Fix: use ROW_NUMBER (Method 1) whenever you need ROWS, not scores.

Performance — The Index That Decides 47s vs 180ms

The composite index (category, price DESC, id) stores each partition pre-sorted: ROW_NUMBER reads partitions in order (sort becomes trivial), LATERAL legs become 2-row index walks, correlated subqueries become index counts. One index serves all methods.

Measurement protocol: EXPLAIN ANALYZE at production row counts (fixtures lie — 200 rows never spill). Watch for Seq Scan, Sort Method: external merge (disk), or Using filesort. Any of those at 10^6+ rows means minutes, and the fix is the index, never query cleverness.

Maintenance note: the index costs write amplification on price/category updates — acceptable for read-heavy catalogs, worth stating in review for write-heavy tables (then consider partial indexes per hot category).

-- Index that serves all three methods:
CREATE INDEX idx_products_cat_price
  ON products (category, price DESC, id ASC);

-- Verify (Postgres): expect Index Scan + bounded Sort per partition
EXPLAIN ANALYZE
WITH ranked AS (
  SELECT id, category, name, price,
         ROW_NUMBER() OVER (PARTITION BY category
                            ORDER BY price DESC, id ASC) AS rn
  FROM products
)
SELECT * FROM ranked WHERE rn <= 2;
-- MySQL: EXPLAIN FORMAT=TREE SELECT ... ; look for no 'filesort' on 10^6 rows.
-- Incident numbers: 2.1M rows, no index -> 47s disk sort; with index -> 180ms.

Interview Playbook — Presenting Top-N Live

Minute 0–1: ask the tie question ('exactly 2, or everyone tied for 2nd?'). Interviewers plant ties in the data; candidates who ask score requirements points before writing SQL. Minutes 1–5: write the ROW_NUMBER CTE with id tiebreak and the outer filter.

Minutes 5–8: name the fallbacks without writing them fully ('correlated COUNT on 5.7, LATERAL for many-groups-indexed'). Minutes 8–10: index + EXPLAIN close. That arc — semantics, default, fallbacks, performance — is the senior-shaped answer.

Red flags to voice: GROUP BY + MAX for rows, window without tiebreak, any ranking query without an index sentence. Voicing them unprompted is what separates hired from 'good SQL'.

-- Whiteboard order: (1) state tie choice, (2) write Method 1, (3) name fallbacks
-- Q: 'Top 2 orders per customer by total?' A: 'Exact 2 or with ties?'
-- Exact: ROW_NUMBER() OVER (PARTITION BY customer_id
--                          ORDER BY total DESC, id) ... WHERE rn <= 2
-- Ties:  RANK() ... (same shape, counts may exceed 2 per customer)
-- Legacy: correlated COUNT (MySQL 5.7) / LATERAL (many groups, indexed)
-- Close: 'index (customer_id, total DESC, id); EXPLAIN at prod rows.'