Top N per Group in SQL

Lesson 11.6 · Filtering windows with QUALIFY kept the single most expensive product in each category. Asking for the top 2 or 3 per group uses the same shape: rank the rows inside each group with partition by, then keep the rows whose rank is N or better. The new question is what to do when two rows tie at the cutoff.

Three answers to "top 2"

Lesson 19.1 showed how the three ranking functions number tied rows. Filtered to <= 2, each one answers a different question:

• row_number() <= 2 returns exactly 2 rows per group. If the second and third rows tie, one of them is dropped, and without a tie-breaker in the ORDER BY the engine chooses which.
• rank() <= 2 returns the first 2 places, and a tie at the cutoff brings every tied row in. A group can return more than 2 rows, but never a row that finished behind 2 others.
• dense_rank() <= 2 returns every row holding one of the 2 highest values. Two rows tied for first don't use up the second value, so the next row comes in too.

with scores(class, student, score) as (
  values ('A', 'Ana', 95), ('A', 'Ben', 90), ('A', 'Cy', 90),
         ('A', 'Dee', 85), ('A', 'Eve', 80)
)
select student, score,
       row_number() over (partition by class order by score desc, student) as row_num,
       rank()       over (partition by class order by score desc)          as rnk,
       dense_rank() over (partition by class order by score desc)          as dense_rnk
from scores
order by score desc, student

Ben and Cy tie for second. Filtered to 2 or less, row_num keeps Ana and Ben, rnk keeps Ana, Ben, and Cy, and so does dense_rnk. Filtered to 3 or less, they part again: rnk still stops at Cy, because Cy's tie used up third place, while dense_rnk brings in Dee with the third-highest score.

Picking one

Use row_number() when the output needs a fixed size, such as 3 slots in a dashboard, and add a tie-breaker you can defend (the earlier date, the lower id). Use rank() for "top N with ties", the rule SQL Server's TOP (n) WITH TIES follows. DuckDB has no WITH TIES, so rank() is how you write it here. Use dense_rank() when the question is about values ("the two highest scores in each class") rather than rows. Whichever you pick, say so next to the report: a reader who sees 3 rows under "top 2" needs to know why. See why rank() = 1 returns more than one row.

This lesson's data

Movie Rentals' 10 customers live in 4 cities. Counting each customer's rentals and ranking them within their city gives some real ties: in Portland, Ava rented 4 movies and Mia and Emma 2 each.