easyDSA

Top-N per group with RANK() OVER (PARTITION BY) — how do you get top 2 per department without collapsing rows?

Q: Top-N per group with RANK() OVER (PARTITION BY) — how do you get top 2 per department without collapsing rows?

`RANK() OVER (PARTITION BY dept ORDER BY score DESC)` numbers rows *within each partition* without collapsing them; wrap in a CTE and filter `WHERE rnk PARTITION BY dept → ORDER BY score DESC → RANK() numbers each partition independently Partition: Engineering Alice 95 → rank 1 Bob 95 → rank 1 Carol 82 → rank 3 (gap!) <text

1.2k views

Understand the problem

Use window RANK() to rank within partitions and filter the ranked CTE; compare with GROUP BY limits.

window-functionsrankpartition

Attempt it yourself

Sketch your approach before reading the solution — that's what interviews test.

Nudge consolestandby

Stuck? Beam a request up — the console returns a conceptual nudge that guides your logic without spoiling the implementation.

Study the solution

RANK() OVER (PARTITION BY dept ORDER BY score DESC) numbers rows within each partition without collapsing them; wrap in a CTE and filter WHERE rnk <= N for top-N per group. Ties share a rank and leave gaps — use DENSE_RANK or ROW_NUMBER when gaps matter.

Solution ready — 2 min read

Classified // press E to declassify

Join the discussion

Discussion (0)

No responses yet. Be the first to share what you think.

Transmission complete // awaiting log

KEEP THE
STREAK ALIVE.

Dossier 2 of 70 decoded in the SQL track. One more won't hurt.

Back to track

Next up→

What is the difference between a primary key and a unique constraint?

Continue