Rank Rows With ROW_NUMBER and RANK in MySQL

Rank rows in MySQL when you need a position number beside each row rather than one summary line per group. A window function reads the same rows an aggregate would, yet it returns every one of them. First, this tutorial numbers a sales table with ROW_NUMBER(). Next, it restarts that numbering per region with PARTITION BY. Then it compares RANK() and DENSE_RANK(), which disagree the moment two rows tie. Finally, it wraps the query in a subquery to pull the top two sellers from every region.

Requirements to rank rows in MySQL:

• MySQL 8.0 or newer (tested on MySQL 8.4.10). Window functions do not exist in MySQL 5.7, so this is a hard floor.
• Terminal access to the mysql client, and a user that can create a database.
• A table with a value worth ordering by. The setup below creates one.

How To Rank Rows in MySQL With ROW_NUMBER and RANK.

The objective is a leaderboard: every salesperson keeps their own row, but each row gains its standing. Create the demo data first, so every query below returns exactly what the article shows.

CREATE DATABASE ndriel_rank_demo;
USE ndriel_rank_demo;

CREATE TABLE sales (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    region      VARCHAR(20) NOT NULL,
    salesperson VARCHAR(40) NOT NULL,
    amount      DECIMAL(10,2) NOT NULL
);

INSERT INTO sales (region, salesperson, amount) VALUES
    ('Europe',       'Anna Novak',    48500.00),
    ('Europe',       'Ben Carter',    52300.00),
    ('Europe',       'Clara Dubois',  48500.00),
    ('Europe',       'David Osei',    31900.00),
    ('Americas',     'Elena Ruiz',    61200.00),
    ('Americas',     'Farid Haddad',  44750.00),
    ('Americas',     'Grace Chen',    61200.00),
    ('Asia Pacific', 'Hiro Tanaka',   37800.00),
    ('Asia Pacific', 'Ingrid Larsen', 55100.00);

Two pairs of rows share an amount on purpose. Anna Novak ties with Clara Dubois, and Elena Ruiz ties with Grace Chen. Those ties are what make the three functions behave differently later.

Step 1.

First, number every row from highest to lowest. The OVER clause is what turns an ordinary function call into a window function.

SELECT ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn,
       salesperson, region, amount
FROM sales;

+----+---------------+--------------+----------+
| rn | salesperson   | region       | amount   |
+----+---------------+--------------+----------+
|  1 | Elena Ruiz    | Americas     | 61200.00 |
|  2 | Grace Chen    | Americas     | 61200.00 |
|  3 | Ingrid Larsen | Asia Pacific | 55100.00 |
|  4 | Ben Carter    | Europe       | 52300.00 |
|  5 | Anna Novak    | Europe       | 48500.00 |
|  6 | Clara Dubois  | Europe       | 48500.00 |
|  7 | Farid Haddad  | Americas     | 44750.00 |
|  8 | Hiro Tanaka   | Asia Pacific | 37800.00 |
|  9 | David Osei    | Europe       | 31900.00 |
+----+---------------+--------------+----------+

Nine rows went in and nine came out. That is the whole difference from grouping and aggregating rows with GROUP BY, which would have collapsed these into one line per region. The ORDER BY inside OVER belongs to the window, not to the result, so it decides the numbering only.

Step 2.

Next, restart the count for each region. PARTITION BY splits the rows into independent windows, and the numbering begins again at 1 inside each one.

SELECT region, salesperson, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
FROM sales
ORDER BY region, rn;

+--------------+---------------+----------+----+
| region       | salesperson   | amount   | rn |
+--------------+---------------+----------+----+
| Americas     | Elena Ruiz    | 61200.00 |  1 |
| Americas     | Grace Chen    | 61200.00 |  2 |
| Americas     | Farid Haddad  | 44750.00 |  3 |
| Asia Pacific | Ingrid Larsen | 55100.00 |  1 |
| Asia Pacific | Hiro Tanaka   | 37800.00 |  2 |
| Europe       | Ben Carter    | 52300.00 |  1 |
| Europe       | Anna Novak    | 48500.00 |  2 |
| Europe       | Clara Dubois  | 48500.00 |  3 |
| Europe       | David Osei    | 31900.00 |  4 |
+--------------+---------------+----------+----+

Note the second ORDER BY, the one after FROM sales. It sorts the finished result for display. Without it the server may hand back the rows in any order, even though the rn values themselves would still be correct.

Step 3.

Then, put the three functions side by side on the tied rows. A WINDOW clause names the window once, so the three calls cannot drift apart.

SELECT salesperson, amount,
       ROW_NUMBER() OVER w AS rn,
       RANK()       OVER w AS rnk,
       DENSE_RANK() OVER w AS dense
FROM sales
WHERE region = 'Europe'
WINDOW w AS (ORDER BY amount DESC)
ORDER BY rn;

+--------------+----------+----+-----+-------+
| salesperson  | amount   | rn | rnk | dense |
+--------------+----------+----+-----+-------+
| Ben Carter   | 52300.00 |  1 |   1 |     1 |
| Anna Novak   | 48500.00 |  2 |   2 |     2 |
| Clara Dubois | 48500.00 |  3 |   2 |     2 |
| David Osei   | 31900.00 |  4 |   4 |     3 |
+--------------+----------+----+-----+-------+

Read the bottom row across, because that is where the three answers separate. ROW_NUMBER() never ties; it broke the 48500.00 pair arbitrarily and gave David Osei 4. RANK() tied that pair at 2, then skipped 3 entirely, so David Osei also lands on 4. DENSE_RANK() tied them too, however it refuses to leave a gap, therefore David Osei becomes 3.

Step 4.

A ranking cannot be filtered where you would expect. Try it and the server stops you outright.

SELECT region, salesperson, amount
FROM sales
WHERE ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) <= 2;

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

The reason is timing. WHERE runs before window functions do, so at that moment the number does not exist yet. As a result, every top-N-per-group query needs one extra layer.

Step 5.

Finally, wrap the ranked query and filter the wrapper. The inner query invents the column; the outer one is free to test it.

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

+--------------+---------------+----------+
| region       | salesperson   | amount   |
+--------------+---------------+----------+
| Americas     | Elena Ruiz    | 61200.00 |
| Americas     | Grace Chen    | 61200.00 |
| Asia Pacific | Ingrid Larsen | 55100.00 |
| Asia Pacific | Hiro Tanaka   | 37800.00 |
| Europe       | Ben Carter    | 52300.00 |
| Europe       | Anna Novak    | 48500.00 |
+--------------+---------------+----------+

The derived table needs a name — AS ranked here — because MySQL rejects one without an alias. For more on that nesting, see writing a subquery in MySQL.

Result when you rank rows in MySQL.

The tie is where the lesson lands. Three functions read identical rows in an identical order, yet they disagree about what position the last row holds. This is the real output from MySQL 8.4.10:

mysql> SELECT salesperson, amount,
    ->        ROW_NUMBER() OVER w AS rn,
    ->        RANK()       OVER w AS rnk,
    ->        DENSE_RANK() OVER w AS dense
    -> FROM sales
    -> WHERE region = 'Europe'
    -> WINDOW w AS (ORDER BY amount DESC)
    -> ORDER BY rn;
+--------------+----------+----+-----+-------+
| salesperson  | amount   | rn | rnk | dense |
+--------------+----------+----+-----+-------+
| Ben Carter   | 52300.00 |  1 |   1 |     1 |
| Anna Novak   | 48500.00 |  2 |   2 |     2 |
| Clara Dubois | 48500.00 |  3 |   2 |     2 |
| David Osei   | 31900.00 |  4 |   4 |     3 |
+--------------+----------+----+-----+-------+
4 rows in set (0.00 sec)

mysql> SELECT region, salesperson, amount
    -> FROM (
    ->     SELECT region, salesperson, amount,
    ->            ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    ->     FROM sales
    -> ) AS ranked
    -> WHERE rn <= 2
    -> ORDER BY region, amount DESC;
+--------------+---------------+----------+
| region       | salesperson   | amount   |
+--------------+---------------+----------+
| Americas     | Elena Ruiz    | 61200.00 |
| Americas     | Grace Chen    | 61200.00 |
| Asia Pacific | Ingrid Larsen | 55100.00 |
| Asia Pacific | Hiro Tanaka   | 37800.00 |
| Europe       | Ben Carter    | 52300.00 |
| Europe       | Anna Novak    | 48500.00 |
+--------------+---------------+----------+
6 rows in set (0.00 sec)

Notes on how to rank rows in MySQL:

• Pick the function by what a tie should mean. Use DENSE_RANK() for medal positions, RANK() when a shared place should consume the next number, and ROW_NUMBER() when you simply need distinct values.
• ROW_NUMBER() breaks ties arbitrarily. Add a tiebreaker such as ORDER BY amount DESC, salesperson if the output must be stable between runs.
• Omitting ORDER BY inside OVER is legal but rarely useful. RANK() then reports 1 for every row, because no row sorts ahead of another.
• The window sees rows that survived WHERE. Step 3 filtered to Europe first, so the ranking covers four rows rather than nine.
• Ranking a large table sorts it. Check with an index read through EXPLAIN — an index matching the PARTITION BY and ORDER BY columns can remove that sort.
• MySQL 8.0 also added NTILE(), LAG() and LEAD(). They take the same OVER clause, so everything above applies to them unchanged.

References:

• MySQL 8.4 Reference Manual — Window Functions
• MySQL 8.4 Reference Manual — Window Function Descriptions
• MySQL 8.4 Reference Manual — Named Windows