Free SQL queries lessonsSQL queries · Stage 6 · Mastery · Step 4

Top N in each group

Pick the top few rows of every group, and choose how ties are treated. About 11 minutes.

Rank inside, filter outside

LIMIT cuts the whole answer, not each group. To get the top two of every department, rank the employees inside each department first, then keep the rows whose rank is 1 or 2.

A window function can't be used in WHERE, so the ranking goes in a subquery or CTE, and the filter goes in the query around it.

Top N per group: rank in a subquery, filter outside.

In code

SELECT name, dept
FROM (
  SELECT name, dept,
         ROW_NUMBER() OVER (
           PARTITION BY dept
           ORDER BY salary DESC
         ) AS n
  FROM employees
)
WHERE n <= 2;

The top two earners of each department

employees

name	dept	salary
Asha	Tech	90
Ravi	Tech	80
Meera	Tech	70
Kabir	Sales	60
Isha	Sales	50
Dev	Sales	40

Tech: Asha is number 1 and Ravi number 2, so both are kept.

Move 1 of 4

Choose how ties are treated

Say three people tie for first place. ROW_NUMBER still numbers them 1, 2, 3, so keeping numbers 1 and 2 drops one of them, and which one is up to the database. Add a tie-break column to the ORDER BY so the choice is deliberate.

If the rule is "everyone with a top-two salary", use DENSE_RANK or RANK instead: all tied people get the same number, so none are lost. Say which you mean.

ROW_NUMBER on a tie: keeps an arbitrary one. Add a tie-break.

In code

-- exactly two rows per department
ROW_NUMBER() OVER (
  PARTITION BY dept
  ORDER BY salary DESC, name
)
-- everyone in the top two salaries
DENSE_RANK() OVER (
  PARTITION BY dept
  ORDER BY salary DESC
)

Quick check

Three employees tie for the top salary in a department. How many of them does ROW_NUMBER() <= 2 keep?

ATwo, and which two isn't guaranteed unless you add a tie-break
BAll three
COne

Show the answer

Two, and which two isn't guaranteed unless you add a tie-break. ROW_NUMBER gives each row a different number, even for equal values, so n <= 2 keeps two.

Your problem

Two best paid per department

The employees table has one row per employee. Show the name, department and salary of the two highest-paid employees in each department. Break salary ties by name A to Z, so each department gives at most two rows. Order by department, then salary highest first, then name.

Tables

employees(id, name, dept, salary)

Up to 2,000 employees · names are unique

Write the query in your browserFree account, no card. Your query runs on real tables, with hints if you get stuck, and your progress is saved.