ROW_NUMBER / RANK / DENSE_RANK

ROW_NUMBER: Assigns a unique sequential number with no duplicates.

ROW_NUMBER() OVER ([PARTITION BY partition_column] ORDER BY sort_column)

RANK: Skips the next number after a tie.

RANK() OVER ([PARTITION BY partition_column] ORDER BY sort_column)

DENSE_RANK: Does not skip numbers after a tie.

DENSE_RANK() OVER ([PARTITION BY partition_column] ORDER BY sort_column)

The following examples use the members table.

Display all three ranking types ordered by score descending.

sample_row_number_rank.sql

SELECT
    name,
    role,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_val
FROM members
ORDER BY score DESC;

+--------+-----------+-------+---------+----------+----------------+
| name   | role      | score | row_num | rank_val | dense_rank_val |
+--------+-----------+-------+---------+----------+----------------+
| user_a | Sales  |   620 |       1 |        1 |              1 |
| user_c | Sales  |   530 |       2 |        2 |              2 |
| user_e | Engineering |   530 |       3 |        2 |              2 |
| user_b | Engineering |   420 |       4 |        4 |              3 |
| user_d | Sales  |   380 |       5 |        5 |              4 |
+--------+-----------+-------+---------+----------+----------------+
5 rows in set

Rank members by score within each role, then retrieve the top 2 per role.

sample_row_number_rank.sql

SELECT name, role, score, role_rank
FROM (
    SELECT
        name,
        role,
        score,
        RANK() OVER (PARTITION BY role ORDER BY score DESC) AS role_rank
    FROM members
) AS m
WHERE role_rank <= 2;

+--------+-----------+-------+-----------+
| name   | role      | score | role_rank |
+--------+-----------+-------+-----------+
| user_a | Sales  |   620 |         1 |
| user_c | Sales  |   530 |         2 |
| user_e | Engineering |   530 |         1 |
| user_b | Engineering |   420 |         2 |
+--------+-----------+-------+-----------+
4 rows in set

ROW_NUMBER(), RANK(), and DENSE_RANK() are supported in MySQL (8.0+), PostgreSQL, Oracle, SQL Server, and SQLite (3.25+).

SELECT
    name,
    role,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_val
FROM members
ORDER BY score DESC;

The key difference between the three functions is how they handle ties. ROW_NUMBER() always assigns a unique number to every row, so tied values still receive different numbers. The order among tied rows is determined by the database engine and is not guaranteed.

RANK() and DENSE_RANK() both assign the same rank to tied rows, but differ in what comes next. Use RANK() when gaps matter (e.g., sports standings where two 2nd-place finishers mean the next rank is 4th), and DENSE_RANK() when you want consecutive ranks without gaps (e.g., two 2nd-place finishers are followed by 3rd).

To retrieve the top N rows, calculate the rank first in a derived table (or CTE), then filter with a WHERE clause in the outer query. This is necessary because window function results cannot be filtered directly in a WHERE clause. For the basic window function syntax, see OVER / PARTITION BY.