Ranking with ROW_NUMBER, RANK and DENSE_RANK

45 min · Intermediate

A window function calculates a value for each row by looking at a set of related rows, called its window, and it keeps every row in the result. Ranking functions use it to give each row a position: 1st, 2nd, 3rd, overall or inside each group.

Window functions keep every row

A window function is a function followed by OVER (...). It calculates a value from a set of related rows but, unlike GROUP BY, it does not merge them: every row stays in the result.

Show every employee next to the average salary of the group. GROUP BY would merge the rows, so use OVER () instead.

GROUP BY versus a window function on 5 employees of departments 2 and 3. GROUP BY AVG(salary) merges them into one row: staff 5, avg_salary 94000.00. AVG(salary) OVER () keeps all 5 rows, each with the same avg_salary 94000.00 added next to its own salary.

Example 1 · GROUP BY merges rows; OVER () keeps them

SQL

1-- GROUP BY style: one summary row2SELECT COUNT(*) AS staff, ROUND(AVG(salary), 2) AS avg_salary3FROM employees4WHERE dept_id IN (2, 3);5
6-- Window style: every row stays, with the summary added7SELECT name,8       dept_id,9       salary,10       ROUND(AVG(salary) OVER (), 2) AS avg_salary,11       COUNT(*) OVER ()              AS staff12FROM employees13WHERE dept_id IN (2, 3)14ORDER BY salary DESC, name;

Output

+-------+------------+
| staff | avg_salary |
+-------+------------+
|     5 |   94000.00 |
+-------+------------+
+---------------+---------+-----------+------------+-------+
| name          | dept_id | salary    | avg_salary | staff |
+---------------+---------+-----------+------------+-------+
| Neha Joshi    |       2 | 150000.00 |   94000.00 |     5 |
| Lakshmi Menon |       3 |  90000.00 |   94000.00 |     5 |
| Amit Shah     |       2 |  85000.00 |   94000.00 |     5 |
| Farah Ali     |       2 |  85000.00 |   94000.00 |     5 |
| Rohan Das     |       3 |  60000.00 |   94000.00 |     5 |
+---------------+---------+-----------+------------+-------+

What this code does

Both queries look at the same 5 employees of Sales and HR. The first collapses them into one row.
The second keeps all 5 rows and adds the same average (94000.00) and count (5) to each. OVER () means the window is every row the query returns, after WHERE.
Now each salary can be compared with the average in the same row, without a subquery.

Each row keeps its own columns and gets the computed value as an extra column. Window functions arrived in MySQL 8.0. GROUP BY answers "what is the average salary?" with one row. Often you need both: every employee and the average next to them, or every student and their position in class. Before window functions this needed a subquery or a self join for each figure. The simplest window is OVER (): all rows of the result. AVG(salary) OVER () puts the average of the whole result on every row, and COUNT(*) OVER () puts the number of rows. The usual aggregates SUM, AVG, COUNT, MIN and MAX all work as window functions.

Predict the output

Predict the window result

Department 3 has two employees: Lakshmi Menon and Rohan Das.

SELECT name, COUNT(*) OVER () AS staff

FROM employees

WHERE dept_id = 3;

OVER and ORDER BY

A window is the set of rows a window function looks at for the current row. It is set inside OVER (...): with ORDER BY the rows are read in that order, and with nothing it is the whole result.

ORDER BY inside OVER decides the order in which the window function looks at the rows. ROW_NUMBER() then numbers the rows 1, 2, 3 in that order: ROW_NUMBER() OVER (ORDER BY price DESC) gives the costliest product number 1.

SQL

SELECT name, priceFROM productsWHERE ROW_NUMBER() OVER (ORDER BY price DESC) <= 3;

Output

ERROR 3593 (HY000): You cannot use the window function 'row_number' in this context.'

Example 2 · ROW_NUMBER: number the products by price

SQL

1SELECT ROW_NUMBER() OVER (ORDER BY price DESC) AS position,2       name,3       price4FROM products5WHERE price > 20006ORDER BY position;

Output

+----------+------------------------+----------+
| position | name                   | price    |
+----------+------------------------+----------+
|        1 | iPhone 15              | 69900.00 |
|        2 | HP Victus Gaming       | 64990.00 |
|        3 | Lenovo IdeaPad Slim 3  | 42990.00 |
|        4 | Samsung Galaxy M35     | 19999.00 |
|        5 | Redmi Note 13          | 17999.00 |
|        6 | Running Shoes          |  2499.00 |
|        7 | Mi Power Bank 20000mAh |  2199.00 |
+----------+------------------------+----------+

What this code does

OVER (ORDER BY price DESC) tells ROW_NUMBER to count from the costliest product down. The iPhone gets 1.
WHERE price > 2000 runs first, so only 7 products are numbered. The final ORDER BY position prints them in that order.

Example 3 · Top 3 with a CTE

SQL

1WITH ranked AS (2    SELECT name,3           price,4           ROW_NUMBER() OVER (ORDER BY price DESC) AS rn5    FROM products6)7SELECT rn, name, price8FROM ranked9WHERE rn <= 310ORDER BY rn;

Output

+----+-----------------------+----------+
| rn | name                  | price    |
+----+-----------------------+----------+
|  1 | iPhone 15             | 69900.00 |
|  2 | HP Victus Gaming      | 64990.00 |
|  3 | Lenovo IdeaPad Slim 3 | 42990.00 |
+----+-----------------------+----------+

What this code does

Inside the CTE the window function runs on all 15 products and rn becomes an ordinary column of ranked.
The outer query can now filter on rn in WHERE, which was impossible in a single query (errors 3593 and 1054).
For a plain top 3, ORDER BY price DESC LIMIT 3 also works. The CTE pattern matters for top N per group and for ties, as the next sections show.

Ranking functions need this ORDER BY: without it ROW_NUMBER numbers the rows in no fixed order, and RANK gives every row 1.

The ORDER BY at the end of the query is separate. It only decides how the final rows are printed. They are often the same, but they do not have to be. You cannot filter on a window function in WHERE, because WHERE runs before window functions are computed. MySQL stops with error 3593, as below, and using the alias (WHERE rn <= 3) gives error 1054, Unknown column. Compute the ranking in a CTE or derived table first, then filter it in the outer query.

ROW_NUMBER versus RANK versus DENSE_RANK

A tie-breaker is an extra ORDER BY column that fixes the order of rows with equal values, such as ORDER BY salary DESC, name. ROW_NUMBER needs one to give a repeatable numbering.

The three ranking functions differ only on ties, rows with the same value. Karthik and Pooja both earn 120000.00:

Example 4 · Three ranking functions side by side

SQL

1SELECT name,2       salary,3       ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS row_num,4       RANK()       OVER (ORDER BY salary DESC)       AS rnk,5       DENSE_RANK() OVER (ORDER BY salary DESC)       AS dense_rnk6FROM employees7ORDER BY salary DESC, name;

Output

+----------------+-----------+---------+-----+-----------+
| name           | salary    | row_num | rnk | dense_rnk |
+----------------+-----------+---------+-----+-----------+
| Vikram Rao     | 250000.00 |       1 |   1 |         1 |
| Anita Desai    | 180000.00 |       2 |   2 |         2 |
| Neha Joshi     | 150000.00 |       3 |   3 |         3 |
| Gita Reddy     | 140000.00 |       4 |   4 |         4 |
| Karthik Raj    | 120000.00 |       5 |   5 |         5 |
| Pooja Gupta    | 120000.00 |       6 |   5 |         5 |
| Suresh Babu    |  95000.00 |       7 |   7 |         6 |
| Lakshmi Menon  |  90000.00 |       8 |   8 |         7 |
| Amit Shah      |  85000.00 |       9 |   9 |         8 |
| Farah Ali      |  85000.00 |      10 |   9 |         8 |
| Manoj Pillai   |  75000.00 |      11 |  11 |         9 |
| Tanvi Kulkarni |  70000.00 |      12 |  12 |        10 |
| Rohan Das      |  60000.00 |      13 |  13 |        11 |
+----------------+-----------+---------+-----+-----------+

What this code does

Look at Karthik Raj and Pooja Gupta (120000.00): row_num gives 5 and 6, rnk gives both 5, dense_rnk gives both 5.
The next person, Suresh Babu, shows the difference: RANK jumps to 7 because two people share 5th place, while DENSE_RANK continues with 6.
ROW_NUMBER has a tie-breaker, name, so Karthik is always 5 and Pooja 6. RANK and DENSE_RANK must not have it, or the tie would disappear.

ROW_NUMBER() always gives different numbers: 5 and 6. Which of the two gets 5 is not defined unless you add a tie-breaker, such as ORDER BY salary DESC, name.
RANK() gives tied rows the same rank and then skips: 5, 5, then 7. This is how a sports table or a competitive exam ranks.
DENSE_RANK() gives tied rows the same rank and does not skip: 5, 5, then 6. Use it for "the second highest salary", where you mean the second distinct value.

The interview favourite is exactly this comparison on a column with ties. Remember the sequence 1, 2, 2, 4 for RANK and 1, 2, 2, 3 for DENSE_RANK.

Predict the output

Predict RANK and DENSE_RANK

Karthik Raj and Pooja Gupta tie at 120000. Suresh Babu earns 95000, the next lower salary.

Round 1 of 2

What is Suresh Babu's dense_rnk?

SELECT name,

DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk

FROM employees;

When you need exactly one row per position, for example for paging or "one winner", use ROW_NUMBER and always add a tie-breaker column so the answer does not change from run to run.

PARTITION BY: ranking per group

PARTITION BY splits the rows into groups inside OVER, and the window function starts again in each group. RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) ranks salaries inside each department.

PARTITION BY splits the rows into groups inside OVER, and the window function starts again in each group. RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) ranks salaries within each department, so every department has its own number 1.

RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) ranks inside each department. In department 2: Neha Joshi 150000 is rank 1, Amit Shah and Farah Ali both 85000 are rank 2. In department 3 the ranking restarts: Lakshmi Menon 90000 is rank 1 and Rohan Das 60000 is rank 2.

Example 5 · Rank inside each department, then keep the top

SQL

1SELECT name,2       dept_id,3       salary,4       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept5FROM employees6WHERE dept_id IS NOT NULL7ORDER BY dept_id, rank_in_dept, name;8
9-- Highest paid person in each department10WITH ranked AS (11    SELECT e.name, d.dept_name, e.salary,12           RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk13    FROM employees e14    JOIN departments d ON d.dept_id = e.dept_id15)16SELECT dept_name, name, salary17FROM ranked18WHERE rnk = 119ORDER BY dept_name;

Output

+---------------+---------+-----------+--------------+
| name          | dept_id | salary    | rank_in_dept |
+---------------+---------+-----------+--------------+
| Vikram Rao    |       1 | 250000.00 |            1 |
| Anita Desai   |       1 | 180000.00 |            2 |
| Karthik Raj   |       1 | 120000.00 |            3 |
| Pooja Gupta   |       1 | 120000.00 |            3 |
| Suresh Babu   |       1 |  95000.00 |            5 |
| Neha Joshi    |       2 | 150000.00 |            1 |
| Amit Shah     |       2 |  85000.00 |            2 |
| Farah Ali     |       2 |  85000.00 |            2 |
| Lakshmi Menon |       3 |  90000.00 |            1 |
| Rohan Das     |       3 |  60000.00 |            2 |
| Gita Reddy    |       4 | 140000.00 |            1 |
| Manoj Pillai  |       4 |  75000.00 |            2 |
+---------------+---------+-----------+--------------+
+-------------+---------------+-----------+
| dept_name   | name          | salary    |
+-------------+---------------+-----------+
| Engineering | Vikram Rao    | 250000.00 |
| Finance     | Gita Reddy    | 140000.00 |
| HR          | Lakshmi Menon |  90000.00 |
| Sales       | Neha Joshi    | 150000.00 |
+-------------+---------------+-----------+

What this code does

PARTITION BY dept_id restarts the ranking in each department: Vikram Rao is 1 in Engineering, Neha Joshi is 1 in Sales.
Ties still behave like RANK: Karthik and Pooja share 3rd in Engineering, so Suresh is 5th; Amit and Farah share 2nd in Sales.
The second query is the top N per group pattern: rank in a CTE, keep rnk = 1. With RANK, a tie for first would keep both people.

Example 6 · Board exam: position in each subject

SQL

1DROP TABLE IF EXISTS board_results;2
3CREATE TABLE board_results (4  student VARCHAR(30) NOT NULL,5  subject VARCHAR(20) NOT NULL,6  marks   TINYINT     NOT NULL,7  PRIMARY KEY (student, subject)8);9
10INSERT INTO board_results VALUES11  ('Ananya',  'Maths',   98), ('Bharath', 'Maths',   95),12  ('Chitra',  'Maths',   98), ('Dev',     'Maths',   90),13  ('Ananya',  'Physics', 88), ('Bharath', 'Physics', 92),14  ('Chitra',  'Physics', 85), ('Dev',     'Physics', 92);15
16SELECT subject,17       student,18       marks,19       DENSE_RANK() OVER (PARTITION BY subject ORDER BY marks DESC) AS position20FROM board_results21ORDER BY subject, position, student;

Output

+---------+---------+-------+----------+
| subject | student | marks | position |
+---------+---------+-------+----------+
| Maths   | Ananya  |    98 |        1 |
| Maths   | Chitra  |    98 |        1 |
| Maths   | Bharath |    95 |        2 |
| Maths   | Dev     |    90 |        3 |
| Physics | Bharath |    92 |        1 |
| Physics | Dev     |    92 |        1 |
| Physics | Ananya  |    88 |        2 |
| Physics | Chitra  |    85 |        3 |
+---------+---------+-------+----------+

What this code does

Each subject is its own partition, so Maths and Physics both start at position 1.
Ananya and Chitra both scored 98 in Maths and share position 1. With DENSE_RANK, Bharath is position 2, which suits a school that announces "first, second and third positions".
With RANK, Bharath would be 3rd, the competitive-exam style. Choose the function by how the institution counts places.

PARTITION BY works like GROUP BY, but without merging rows: every employee is still there, now with a position inside their own department. You can partition by any column or expression: subject, city, month or category.

The most common use is top N per group: the highest-paid person in each department, the best-selling product in each category, the topper in each subject. Rank with PARTITION BY in a CTE, then keep the rows with rank 1 (or <= 3) in the outer query.

Fix the error

Fix the missing partition

The rank should restart in every department.

Lakshmi Menon, the top of HR, gets rank 8 instead of 1.

SELECT name, dept_id, salary,

RANK() OVER (ORDER BY salary DESC) AS dept_rank

FROM employees;

Error

No error, but dept_rank counts across the whole company.

Next step

Check your understanding

12 quick questions

1-- GROUP BY style: one summary row2SELECT COUNT(*) AS staff, ROUND(AVG(salary), 2) AS avg_salary3FROM employees4WHERE dept_id IN (2, 3);5 6-- Window style: every row stays, with the summary added7SELECT name,8 dept_id,9 salary,10 ROUND(AVG(salary) OVER (), 2) AS avg_salary,11 COUNT(*) OVER () AS staff12FROM employees13WHERE dept_id IN (2, 3)14ORDER BY salary DESC, name;

+-------+------------+ | staff | avg_salary | +-------+------------+ | 5 | 94000.00 | +-------+------------+ +---------------+---------+-----------+------------+-------+ | name | dept_id | salary | avg_salary | staff | +---------------+---------+-----------+------------+-------+ | Neha Joshi | 2 | 150000.00 | 94000.00 | 5 | | Lakshmi Menon | 3 | 90000.00 | 94000.00 | 5 | | Amit Shah | 2 | 85000.00 | 94000.00 | 5 | | Farah Ali | 2 | 85000.00 | 94000.00 | 5 | | Rohan Das | 3 | 60000.00 | 94000.00 | 5 | +---------------+---------+-----------+------------+-------+

OVER and ORDER BY

A window is the set of rows a window function looks at for the current row. It is set inside OVER (...): with ORDER BY the rows are read in that order, and with nothing it is the whole result.

+----------+------------------------+----------+ | position | name | price | +----------+------------------------+----------+ | 1 | iPhone 15 | 69900.00 | | 2 | HP Victus Gaming | 64990.00 | | 3 | Lenovo IdeaPad Slim 3 | 42990.00 | | 4 | Samsung Galaxy M35 | 19999.00 | | 5 | Redmi Note 13 | 17999.00 | | 6 | Running Shoes | 2499.00 | | 7 | Mi Power Bank 20000mAh | 2199.00 | +----------+------------------------+----------+

+----+-----------------------+----------+ | rn | name | price | +----+-----------------------+----------+ | 1 | iPhone 15 | 69900.00 | | 2 | HP Victus Gaming | 64990.00 | | 3 | Lenovo IdeaPad Slim 3 | 42990.00 | +----+-----------------------+----------+

1SELECT name,2 salary,3 ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS row_num,4 RANK() OVER (ORDER BY salary DESC) AS rnk,5 DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk6FROM employees7ORDER BY salary DESC, name;

+----------------+-----------+---------+-----+-----------+ | name | salary | row_num | rnk | dense_rnk | +----------------+-----------+---------+-----+-----------+ | Vikram Rao | 250000.00 | 1 | 1 | 1 | | Anita Desai | 180000.00 | 2 | 2 | 2 | | Neha Joshi | 150000.00 | 3 | 3 | 3 | | Gita Reddy | 140000.00 | 4 | 4 | 4 | | Karthik Raj | 120000.00 | 5 | 5 | 5 | | Pooja Gupta | 120000.00 | 6 | 5 | 5 | | Suresh Babu | 95000.00 | 7 | 7 | 6 | | Lakshmi Menon | 90000.00 | 8 | 8 | 7 | | Amit Shah | 85000.00 | 9 | 9 | 8 | | Farah Ali | 85000.00 | 10 | 9 | 8 | | Manoj Pillai | 75000.00 | 11 | 11 | 9 | | Tanvi Kulkarni | 70000.00 | 12 | 12 | 10 | | Rohan Das | 60000.00 | 13 | 13 | 11 | +----------------+-----------+---------+-----+-----------+

PARTITION BY: ranking per group

1SELECT name,2 dept_id,3 salary,4 RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept5FROM employees6WHERE dept_id IS NOT NULL7ORDER BY dept_id, rank_in_dept, name;8 9-- Highest paid person in each department10WITH ranked AS (11 SELECT e.name, d.dept_name, e.salary,12 RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk13 FROM employees e14 JOIN departments d ON d.dept_id = e.dept_id15)16SELECT dept_name, name, salary17FROM ranked18WHERE rnk = 119ORDER BY dept_name;

+---------------+---------+-----------+--------------+ | name | dept_id | salary | rank_in_dept | +---------------+---------+-----------+--------------+ | Vikram Rao | 1 | 250000.00 | 1 | | Anita Desai | 1 | 180000.00 | 2 | | Karthik Raj | 1 | 120000.00 | 3 | | Pooja Gupta | 1 | 120000.00 | 3 | | Suresh Babu | 1 | 95000.00 | 5 | | Neha Joshi | 2 | 150000.00 | 1 | | Amit Shah | 2 | 85000.00 | 2 | | Farah Ali | 2 | 85000.00 | 2 | | Lakshmi Menon | 3 | 90000.00 | 1 | | Rohan Das | 3 | 60000.00 | 2 | | Gita Reddy | 4 | 140000.00 | 1 | | Manoj Pillai | 4 | 75000.00 | 2 | +---------------+---------+-----------+--------------+ +-------------+---------------+-----------+ | dept_name | name | salary | +-------------+---------------+-----------+ | Engineering | Vikram Rao | 250000.00 | | Finance | Gita Reddy | 140000.00 | | HR | Lakshmi Menon | 90000.00 | | Sales | Neha Joshi | 150000.00 | +-------------+---------------+-----------+

1DROP TABLE IF EXISTS board_results;2 3CREATE TABLE board_results (4 student VARCHAR(30) NOT NULL,5 subject VARCHAR(20) NOT NULL,6 marks TINYINT NOT NULL,7 PRIMARY KEY (student, subject)8);9 10INSERT INTO board_results VALUES11 ('Ananya', 'Maths', 98), ('Bharath', 'Maths', 95),12 ('Chitra', 'Maths', 98), ('Dev', 'Maths', 90),13 ('Ananya', 'Physics', 88), ('Bharath', 'Physics', 92),14 ('Chitra', 'Physics', 85), ('Dev', 'Physics', 92);15 16SELECT subject,17 student,18 marks,19 DENSE_RANK() OVER (PARTITION BY subject ORDER BY marks DESC) AS position20FROM board_results21ORDER BY subject, position, student;

+---------+---------+-------+----------+ | subject | student | marks | position | +---------+---------+-------+----------+ | Maths | Ananya | 98 | 1 | | Maths | Chitra | 98 | 1 | | Maths | Bharath | 95 | 2 | | Maths | Dev | 90 | 3 | | Physics | Bharath | 92 | 1 | | Physics | Dev | 92 | 1 | | Physics | Ananya | 88 | 2 | | Physics | Chitra | 85 | 3 | +---------+---------+-------+----------+