SQL Interview Prep · Lesson

Top-N Rows Per Group With ROW_NUMBER

Q: Is the “Top-N Rows Per Group With ROW_NUMBER” lesson free?

Yes — the full text of “Top-N Rows Per Group With ROW_NUMBER” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

Q: Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

Q: How long does the “Top-N Rows Per Group With ROW_NUMBER” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Q: Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

The canonical partition-and-rank pattern for 'top 3 per category'.

Top-N Rows Per Group With ROW_NUMBER is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Top-N Per Group Question

One of the most common SQL interview prompts sounds simple: "Return the top 3 highest-paid employees in each department." Candidates who reach for LIMIT immediately fail, because LIMIT caps the whole result set, not each group.

The interviewer is checking whether you know window functions. The canonical answer is: number the rows inside each group, then keep the rows whose number is ≤ N. This lesson builds that pattern step by step.

Why LIMIT Cannot Solve It

Suppose you write the query below. It returns only 3 rows total across the entire table, not 3 per department.

LIMIT (or TOP, or FETCH FIRST) operates on the final result set. There is no per-group LIMIT in standard SQL. When an interviewer hears you suggest LIMIT 3 for a per-group problem, it signals you have not internalized partitioning.

-- WRONG: only 3 rows total, not 3 per department
SELECT department, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Meet ROW_NUMBER

ROW_NUMBER() is a window function that assigns a unique, gapless integer to each row according to an ordering. By itself it numbers the whole result.

The magic ingredient is PARTITION BY: it restarts the numbering at 1 for every group. Combine PARTITION BY department with ORDER BY salary DESC and each department gets its own 1, 2, 3, ... ranking by salary.

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS rn
FROM employees;

Reading the Numbered Output

After running the previous query, every row carries an rn value. Within each department the highest salary gets rn = 1, the next gets 2, and so on. A new department resets back to 1.

Sales: Ana (1), Bo (2), Cal (3), Dee (4)
Engineering: Eve (1), Fin (2), Gus (3)

Now "top 3 per department" simply means "keep rows where rn <= 3".

You Cannot Filter rn in WHERE

The natural next step is WHERE rn <= 3, but it fails. Window functions are computed after the WHERE clause in logical execution order, so the alias rn does not yet exist when WHERE runs.

Interviewers love this trap. The fix is to compute the window function in a subquery or CTE, then filter the result of that inner query in an outer query.

-- ERROR: rn does not exist in WHERE
SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 3;

The Canonical CTE Solution

Wrap the numbering in a CTE named ranked, then select from it with the filter in the outer WHERE. This is the answer interviewers want to see and it reads cleanly.

Memorize this skeleton: partition by the group, order by the metric, filter rn ≤ N in the outer query. It generalizes to top-1, top-5, or any N by changing one number.

WITH ranked AS (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 3
ORDER BY department, rn;

The Subquery Form

If the interviewer's dialect is older or they prefer subqueries, the identical logic fits inside a derived table in FROM. Remember a derived table must have an alias (r here) or you get a syntax error.

CTE and derived-table forms are interchangeable for this problem. Pick whichever the interviewer finds more readable; both are equally correct.

SELECT name, department, salary
FROM (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
) AS r
WHERE rn <= 3;

Top-1: The Single Best Per Group

"Find the single highest-paid employee in each department" is just N = 1. Set the filter to rn = 1.

Why not MAX(salary) with GROUP BY department? Because MAX gives you the salary value but not the rest of that employee's row (their name, hire date, etc.). ROW_NUMBER keeps the whole winning row intact, which is usually what the question really wants.

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
)
SELECT name, department, salary, hire_date
FROM ranked
WHERE rn = 1;

Adding a Deterministic Tiebreaker

ROW_NUMBER always returns exactly N rows, even when salaries tie. But which tied row gets rn = 1 is arbitrary unless you break the tie. If two people earn 90000 and you only keep rn = 1, the chosen one is unpredictable across runs.

Add a secondary, unique sort key such as employee_id so the result is stable and reproducible. Interviewers reward candidates who mention determinism unprompted.

ROW_NUMBER() OVER (
  PARTITION BY department
  ORDER BY salary DESC, employee_id ASC
) AS rn

A Concrete Worked Example

Given a sales table with region, product, and revenue, return the top 2 products by revenue per region. Same recipe: partition by region, order by revenue DESC, keep rn <= 2.

Notice how only the partition column and the metric column change. The structure is identical regardless of the business domain.

WITH ranked AS (
  SELECT region, product, revenue,
         ROW_NUMBER() OVER (
           PARTITION BY region ORDER BY revenue DESC, product
         ) AS rn
  FROM sales
)
SELECT region, product, revenue
FROM ranked
WHERE rn <= 2
ORDER BY region, rn;

Performance and Talking Points

To impress beyond correctness, mention:

An index on (department, salary DESC) helps the engine produce ordered rows per partition efficiently.
The window approach scans the table once, far better than a correlated subquery that runs per row.
For very large top-N-of-1 cases, some engines support DISTINCT ON (Postgres) as a shortcut, but ROW_NUMBER is the portable standard.

Always state your tiebreaker and confirm the requested N.

Quick Check

Test your grasp of the top-N-per-group pattern.

Recap: Top-N Per Group

The pattern in one breath: partition by the group, order by the metric, assign ROW_NUMBER, then keep rn ≤ N in an outer query.

LIMIT caps the whole set, never per group.
You cannot filter the window alias in WHERE; wrap it in a CTE or subquery.
Add a unique tiebreaker for deterministic results.
Top-1 keeps the entire winning row, unlike MAX + GROUP BY.

Change one number and the same query solves top-1, top-5, or any N.

Frequently asked questions

Is the “Top-N Rows Per Group With ROW_NUMBER” lesson free?

Yes — the full text of “Top-N Rows Per Group With ROW_NUMBER” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Top-N Rows Per Group With ROW_NUMBER”?

The canonical partition-and-rank pattern for 'top 3 per category'. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Top-N Rows Per Group With ROW_NUMBER” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep