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?

**Target Audience:** Beginners who have seen `GROUP BY` and want to return the best N rows of every group, and candidates preparing for the tie and performance follow-ups. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. The window-function pattern is standard SQL and also works in MySQL 8.0+ and SQL Server. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A school holds a sports day with four houses. The head teacher wants a notice board: "the top two runners of each house". She does not want "the fastest time per house" (that is one number per house and no names), and she

The problem

Question presented to candidate: "We have an employees table with dept and salary. Give me the two highest-paid people in every department, with their names and salaries, as normal rows. What happens when two people tie for second place?"

What a strong answer should cover:

Why GROUP BY dept with MAX(salary) is the wrong tool: it collapses each department to one row and loses the names; a window function keeps every row and adds a number to it
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) numbers each department separately, then an outer query (CTE or derived table) filters WHERE rnk <= 2
Why the filter cannot go in the same WHERE: window functions are computed after WHERE, so both SQLite and PostgreSQL reject it
Ties: RANK() <= 2 can return more than two rows per group, ROW_NUMBER() returns exactly two (add a tiebreaker), DENSE_RANK() <= 2 returns the top two distinct salaries
Alternatives and cost: a correlated COUNT(*) subquery (works without window functions), LATERAL ... LIMIT with an index for few rows per group, QUALIFY on engines that have it; PostgreSQL 15+ can stop the window early (Run Condition)

Clarifying questions expected:

If two people tie for second place, should both appear, or exactly two rows per department?
Do departments with fewer than two people still appear (yes, with whoever they have)?
How big is the table and is there an index on (dept, salary) — is this a report or a hot API path?

Code / implementation expected: Yes — a ranked CTE filtered in an outer query, and an explanation of how the choice of ranking function changes the result on ties.

Schema used by the examples:

window-functions
rank
partition

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who have seen GROUP BY and want to return the best N rows of every group, and candidates preparing for the tie and performance follow-ups. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. The window-function pattern is standard SQL and also works in MySQL 8.0+ and SQL Server.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A school holds a sports day with four houses. The head teacher wants a notice board: "the top two runners of each house".

The solution is hidden so you can try first

8 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLTop 2 per department with RANK() in a CTE (ties are kept)

SQLROW_NUMBER vs RANK vs DENSE_RANK side by side on the tied departments

SQLWithout window functions: a correlated COUNT(*) gives the same rows as RANK() <= 2

PostgreSQL: the plan shows a Run Condition on the window

EXPLAIN (COSTS OFF)
SELECT dept, name, salary
FROM (
  SELECT dept, name, salary,
         RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
) AS ranked
WHERE rnk <= 2;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.