Top N Per Group in SQL: Every Method Compared | sqlinterview

What "Top N Per Group" Means

Top N per group is one of the most common real-world SQL patterns: the top 3 highest-paid employees in each department, the most recent 5 orders per customer, the best-selling product in each category. It's asked constantly in interviews because a naive first attempt (a simple GROUP BY with LIMIT) can't actually express it — LIMIT applies to the whole result set, not per group — so the question reveals whether a candidate reaches for the right tool.

The Sample Data

-- employees
| id | name    | department  | salary |
|----|---------|-------------|--------|
| 1  | Alice   | Engineering | 120000 |
| 2  | Bob     | Engineering | 110000 |
| 3  | Charlie | Engineering | 95000  |
| 4  | Diana   | Marketing   | 90000  |
| 5  | Evan    | Marketing   | 85000  |
| 6  | Farah   | Sales       | 100000 |

Goal: the top 2 highest-paid employees in each department.

Method 1: Window Function (the standard modern answer)

SELECT name, department, salary FROM (
  SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn <= 2;

-- Result:
-- | name    | department  | salary |
-- |---------|-------------|--------|
-- | Alice   | Engineering | 120000 |
-- | Bob     | Engineering | 110000 |
-- | Diana   | Marketing   | 90000  |
-- | Evan    | Marketing   | 85000  |
-- | Farah   | Sales       | 100000 |

This is the answer most interviewers want by default: partition the data by group, rank within each partition, then filter the outer query on the rank.

ROW_NUMBER vs. RANK vs. DENSE_RANK — Which One Here?

The choice matters a lot the moment there's a tie for the cutoff position:

ROW_NUMBER() — always returns exactly N rows per group, arbitrarily breaking ties. Use this when you need a strict, fixed row count (e.g. "exactly 2 rows per department, no more").
RANK() — if two employees tie for 2nd place, both get rank 2, and WHERE rnk <= 2 would correctly return both, but the next distinct rank jumps to 4.
DENSE_RANK() — same tie behavior as RANK, but ranks stay contiguous afterward. Use this when "top 2" should mean "top 2 distinct salary levels," potentially returning more than 2 rows if there's a tie.

Stating this distinction out loud, unprompted, is one of the strongest signals you can give in an interview — see our Nth highest salary guide for a deeper walkthrough of exactly this tradeoff.

Method 2: Correlated Subquery (portable, no window functions required)

SELECT e1.name, e1.department, e1.salary
FROM employees e1
WHERE (
  SELECT COUNT(*) FROM employees e2
  WHERE e2.department = e1.department AND e2.salary > e1.salary
) < 2;

Counts how many people in the same department earn more; if fewer than 2 do, this row is in the top 2. Works on databases without window function support, but scales worse — it's effectively a nested loop, re-scanning the department for every row.

Method 3: LATERAL Join (PostgreSQL) — the most efficient at scale

When groups are large but you only need a handful of rows from each, a LATERAL join lets the database use an index to fetch each group's top rows directly, rather than ranking every single row in the table before filtering.

SELECT d.department, top.name, top.salary
FROM (SELECT DISTINCT department FROM employees) d
CROSS JOIN LATERAL (
  SELECT name, salary
  FROM employees e
  WHERE e.department = d.department
  ORDER BY salary DESC
  LIMIT 2
) top;

With a composite index on (department, salary DESC), this can be dramatically faster than the window function approach on very large tables with many groups, because the database can seek directly to each group's top rows via the index instead of computing and sorting a rank for every row in the entire table.

Method 4: CROSS APPLY (SQL Server)

SQL Server's equivalent to LATERAL is CROSS APPLY, with the same performance characteristics and the same underlying idea — run a correlated, indexable subquery per outer row.

SELECT d.department, top.name, top.salary
FROM (SELECT DISTINCT department FROM employees) d
CROSS APPLY (
  SELECT TOP 2 name, salary
  FROM employees e
  WHERE e.department = d.department
  ORDER BY salary DESC
) top;

Window Function vs. LATERAL/APPLY: Which Should You Use?

Window Function (Method 1)	LATERAL / CROSS APPLY (Method 3/4)
Simpler to write and read	More verbose
Ranks every row in the table, then filters	Can use an index to fetch only the needed rows per group directly
Best for small-to-medium tables, or when N is a large fraction of each group	Best for huge tables with many groups and a small, fixed N (e.g. "top 3 of 10,000 categories")

In practice: default to the window function approach for readability, and reach for LATERAL/APPLY specifically when you've confirmed (via EXPLAIN ANALYZE) that the window function version is scanning and ranking far more rows than it needs to.

Top N Per Group With a Tiebreaker

Real data often needs a secondary sort to break ties deterministically — otherwise "top 2" can return a different arbitrary row on each run when using ROW_NUMBER.

SELECT name, department, salary FROM (
  SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, id ASC) AS rn
  FROM employees
) ranked
WHERE rn <= 2;

Adding a stable tiebreaker column (like id) to the ORDER BY is a small detail that signals real production experience — interviewers notice when a candidate adds it unprompted.

Common Mistakes

Using GROUP BY with LIMIT and expecting per-group limiting — LIMIT applies to the whole query result, not each group; this doesn't express "top N per group" at all.
Forgetting PARTITION BY — without it, the ranking runs across the entire table instead of resetting per group.
Not thinking about ties — defaulting to ROW_NUMBER without considering whether DENSE_RANK better matches the actual business question.
Filtering rank in the same query level it's computed — window functions can't be filtered directly in the same-level WHERE; always wrap in a subquery/CTE first (see our window functions guide for why).

Common Interview Questions

Write a query for the top 3 best-selling products in each category. Method 1 — ROW_NUMBER partitioned by category, ordered by sales descending, filtered to rn <= 3.
How would you solve this on a database without window function support? The correlated subquery approach (Method 2).
How would you make this query faster on a table with millions of rows and thousands of groups? LATERAL/CROSS APPLY with a supporting composite index (Method 3/4) — explain the indexed-seek-per-group advantage.
What happens if two products are tied for 3rd place in a category? Depends on the ranking function chosen — walk through ROW_NUMBER vs RANK vs DENSE_RANK behavior explicitly.

Frequently Asked Questions

Does "top N per group" always need a window function?

No — it's the most common and readable solution, but correlated subqueries (Method 2) and LATERAL/APPLY (Method 3/4) solve the same problem with different performance and compatibility tradeoffs, as covered above.

Can I get top N per group without a subquery wrapper?

Not with the window function approach — since window functions can't be filtered in the same query level where they're computed, a subquery or CTE wrapper is required. The LATERAL/APPLY approach avoids this specific requirement, since the LIMIT/TOP happens inside the correlated subquery itself.

Practice This Pattern

Top N per group is one of the highest-frequency patterns in both interviews and real analytics work — worth having fluent in at least two of the methods above. Try it hands-on with our SQL practice questions, or apply it to a full business dataset in our case studies. For the ranking mechanics behind Method 1, see our complete guide to window functions.