Ranking rows and top N per group

Number rows within each group with ROW_NUMBER, RANK and DENSE_RANK, then keep the top few or remove duplicates.

“Each rep’s biggest deal.” “The three most recent orders per customer.” “The latest record for each account.” These are top-N-per-group questions, and LIMIT can’t answer them, because LIMIT only cuts the whole result, not each group. Ranking window functions can.

Three ways to number rows

All three use OVER (ORDER BY ...). The ORDER BY inside the parentheses decides who’s first:

SELECT name,
       amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
       RANK()       OVER (ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk
FROM opportunities
WHERE closed_at IS NULL;

They only differ on ties:

Watch ROW_NUMBER on ties: Cedar Expansion and Prairie Security Bundle tie at $150,000, and which one gets 1 is up to the database. It can change between runs. If that matters, add a tiebreaker: ORDER BY amount DESC, opportunity_id.

Ranking within each group

Add PARTITION BY, as in lesson 29, and the numbering restarts for every group:

SELECT owner_id,
       name,
       amount,
       RANK() OVER (PARTITION BY owner_id ORDER BY amount DESC) AS rnk
FROM opportunities
WHERE is_won = 1;

Each rep’s biggest won deal gets rank 1, then 2, and so on. Ben Carter’s two won deals at $30,000 both get rank 4, and his next deal is rank 6.

Top N per group

You can’t filter on a window function in WHERE (lesson 29), so rank in a CTE, then filter:

WITH ranked AS (
    SELECT owner_id,
           name,
           amount,
           RANK() OVER (PARTITION BY owner_id ORDER BY amount DESC) AS rnk
    FROM opportunities
    WHERE is_won = 1
)
SELECT owner_id, name, amount, rnk
FROM ranked
WHERE rnk <= 2;

That’s each rep’s two biggest won deals. Switch RANK for ROW_NUMBER if you want exactly two, even when deals tie.

Keep one row per key: deduplication

Source data often has several rows per thing when you only want one, like the latest. Number each group’s rows newest first, then keep number 1:

WITH ranked AS (
    SELECT account_id,
           name,
           created_at,
           ROW_NUMBER() OVER (PARTITION BY account_id
                              ORDER BY created_at DESC) AS rn
    FROM opportunities
)
SELECT account_id, name, created_at
FROM ranked
WHERE rn = 1;

One row per account, its most recent deal: 13 accounts have deals, so 13 rows. This is the standard pattern for cleaning up repeated rows from a CRM export, and it’s the most common window function in real pipelines. Use ROW_NUMBER here, not RANK: you want exactly one row per account, even if two deals were created on the same day.

Common mistakes

Dialect notes

ROW_NUMBER, RANK and DENSE_RANK work the same in SQL Server, PostgreSQL, MySQL 8.0+ and SQLite. SQL Server’s SELECT TOP 1 WITH TIES ... ORDER BY is a whole-result shortcut, not a per-group one.

Snowflake notes

With QUALIFY (lesson 29), top N per group needs no CTE:

SELECT owner_id, name, amount
FROM opportunities
WHERE is_won = 1
QUALIFY RANK() OVER (PARTITION BY owner_id ORDER BY amount DESC) <= 2;

The dedup pattern becomes QUALIFY ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY created_at DESC) = 1, which you’ll see in nearly every dbt staging model. MAX_BY(name, created_at) is a shortcut when you only need one column from the latest row. None of these run in the sandbox.

More in the field guide: ROW_NUMBER / RANK / DENSE_RANK and Deduplication pattern. Exercises A1 and A3 use both patterns.

Try it

Show each rep’s top 4 won deals by amount. When deals tie, keep all of them, so a rep can have more than 4. Leave out deals with no owner. Return the owner_id, deal name and amount.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson