Free SQL queries lessonsSQL queries · Stage 4 · Subqueries and window functions · Step 4

Rank within a group

Number rows inside each group with ROW_NUMBER, RANK and DENSE_RANK. About 11 minutes.

A column that looks at its neighbours

GROUP BY squeezes each group into one row. A window function keeps every row and adds a new column that was worked out by looking at a set of related rows.

The window is described after OVER. PARTITION BY names the groups, and ORDER BY inside the brackets says how to line the rows up within each group. RANK() then numbers them 1, 2, 3 and so on, starting again for each group.

OVER (PARTITION BY g ORDER BY x): a ranked column, group by group.

In code

SELECT name, dept, salary,
       RANK() OVER (
         PARTITION BY dept
         ORDER BY salary DESC
       ) AS rk
FROM employees;

Three ways to number

They differ only on ties. ROW_NUMBER never repeats: tied rows get different numbers, in no promised order. RANK repeats the number for ties and then skips, so 1, 1, 3. DENSE_RANK repeats and doesn't skip, 1, 1, 2.

You can't filter on a window column in WHERE, because windows are computed after it. Put the query in a subquery or a CTE, then filter the rank outside.

WHERE rk = 1: doesn't work. Wrap it in a subquery.

In code

SELECT name, dept
FROM (
  SELECT name, dept,
         RANK() OVER (
           PARTITION BY dept
           ORDER BY salary DESC
         ) AS rk
  FROM employees
)
WHERE rk = 1;

Ranks inside each department

employees

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

PARTITION BY dept: the three Tech rows form one window, ordered by salary, highest first.

Move 1 of 4

Quick check

Three people tie for the top salary and the next person is lower. What does RANK() give the next person?

Show the answer

4. Three rows share rank 1, so RANK skips to 4. DENSE_RANK would give 2.

Your problem

Top earner per department

The employees table has one row per employee. Show the name, department and salary of the highest-paid employee in each department. If two employees tie for the highest salary in a department, show both. Order by department, 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.