Top N per group

The idea

Every leaderboard question (top 3 reps per region, best 5 products per category, latest login per user) is the same two moves. First you number the rows inside each group, best first. Then you throw away every row whose number is bigger than N.

The two moves cannot live in the same query level. A window function is computed after WHERE has already run, so there is nothing to filter on yet; you need an outer query (a CTE or subquery) that can see the number.

The only real decision left is what happens when two rows tie right on the line. ROW_NUMBER() always gives you exactly N and breaks the tie by whatever you put last in ORDER BY. RANK() refuses to separate equals and hands you N+1 rows. Neither is wrong: they answer different questions.

Rank, then keep

Move the keep-line to N = 3 and switch functions: north has two reps on 740. That single tie is the whole lesson.

How it works

SQL evaluates a select in a fixed logical order: FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → ORDER BY. Window functions come after WHERE and HAVING, which is why WHERE ROW_NUMBER() OVER (...) <= 3 is a syntax error everywhere. Compute the number in an inner block, filter it in an outer one.

-- step 1: number the rows inside each partition. Nothing is filtered here.
WITH ranked AS (
    SELECT
        region,
        name,
        sales,
        id,
        ROW_NUMBER() OVER (
            PARTITION BY region
            ORDER BY sales DESC, id ASC     -- id = deterministic tiebreak
        ) AS rn
    FROM sales_rep
)
-- step 2: filter the ranking from the outside.
SELECT region, name, sales, id, rn
FROM ranked
WHERE rn <= 3
ORDER BY region, rn;

Swap in RANK() when the contract is "nobody tied with third place gets cut": the group may then return more than three rows. Some engines (Snowflake, BigQuery, DuckDB, Teradata) let you skip the wrapper with QUALIFY rn <= 3, which is the same two steps with nicer syntax.

Cost

Trade-off: the window version reads and sorts the whole table once. Simple, portable, and the right default. The lateral version touches only g · N rows via an index, so it wins hard when there are few groups and millions of rows, but it needs a list of group keys to drive it and repeats an index probe per group.

Watch out for

• Filtering the window where you computed it. WHERE rn <= 3 in the same select fails, and so does moving it to HAVING: both run before window functions. Wrap it in a CTE or subquery, or use QUALIFY if your engine has it.
• Assuming "rank ≤ N" means "at most N rows". RANK() returns N+k rows when k extra rows tie on the boundary, and DENSE_RANK() <= 3 can return dozens: it keeps every row sharing the top three distinct values. If a downstream job pays exactly three bonuses, only ROW_NUMBER() guarantees three.
• ROW_NUMBER with a non-unique ORDER BY. If two rows are equal on every ordering key, which one gets rank 3 is arbitrary and can change between runs, plans, or parallel workers. Always end the OVER (... ORDER BY ...) with a unique column, and write down that the tiebreak is part of the business rule.
• NULLs, twice. Under ORDER BY sales DESC, PostgreSQL and Oracle sort NULLs first: a rep with no recorded sales becomes rank 1. Say DESC NULLS LAST. And every row with a NULL region collapses into one shared partition rather than being skipped.
• Filter placement changes the answer. WHERE status = 'active' inside the CTE ranks only active reps and gives a full top 3. The same predicate in the outer query ranks everyone first, then punches holes: you may get back one row for a group. Both are legitimate; pick the one that matches the question.

Worked example

The quarterly bonus pool pays the top three reps in each region. In north the sorted sales are Ivy 920, Hana 880, Gus 740, Finn 740, Eve 610. RANK() stamps 1, 2, 3, 3, 5, so rank <= 3 returns four people: finance receives four bonus rows for a three-bonus budget.

Switching to ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales DESC, id ASC) stamps 1, 2, 3, 4 and returns exactly three. Finn is cut not on merit but because his id, 27, sorts after Gus's 12. That is a real business decision hiding in a tiebreak column, so make it a defensible one (earliest hire date, or highest units sold) and put it in the spec.

The public leaderboard on the intranet is a different question: there, showing Gus and Finn as joint third is exactly right, so the display query uses RANK() and the payroll query uses ROW_NUMBER(). Same table, same partition, two intentional answers.

Check yourself