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)

-- Display all three ranking types ordered by salary descending.
SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK()       OVER (ORDER BY salary DESC) AS rank_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_dense
FROM employees
ORDER BY salary DESC;

-- Rank employees by salary within each department, then retrieve the top 2 per department.
SELECT employee_name, department, salary, dept_rank
FROM (
    SELECT
        employee_name,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
    FROM employees
) AS ranked
WHERE dept_rank <= 2;

-- Difference between the three rankings (two employees tied at salary 530000).
employee_name | salary  | row_num | rank_rank | rank_dense
--------------+---------+---------+-----------+-----------
Suzuki Hanako | 620000  | 1       | 1         | 1
Yamada Taro   | 530000  | 2       | 2         | 2
Sato Saburo   | 530000  | 3       | 2         | 2
Tanaka Megumi | 420000  | 4       | 4         | 3
Takahashi I.  | 380000  | 5       | 5         | 4

-- Top 2 employees per department.
employee_name | department | salary  | dept_rank
--------------+------------+---------+-----------
Suzuki Hanako | Engineering| 620000  | 1
Sato Saburo   | Engineering| 530000  | 2
Yamada Taro   | Sales      | 480000  | 1
Tanaka Megumi | Sales      | 320000  | 2

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

-- Display all three ranking types ordered by salary descending (compatible with all major databases).
SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK()       OVER (ORDER BY salary DESC) AS rank_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_dense
FROM employees
ORDER BY salary 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.