How to Use ROW_NUMBER and DENSE_RANK in MySQL

This article explains how to write MySQL queries using the ROW_NUMBER() and DENSE_RANK() window functions. You will learn the fundamental syntax of these functions, understand how they differ when ranking data, and see practical query examples that you can apply directly to your database.

Understanding Window Functions in MySQL

Window functions perform calculations across a set of table rows that are related to the current row. Unlike regular aggregate functions, window functions do not group multiple rows into a single output row; instead, they retain the identity of each individual row in the result set.

To use window functions like ROW_NUMBER() or DENSE_RANK(), you must use the OVER clause, which defines the “window” of rows the function operates on. The OVER clause typically contains two components: * PARTITION BY: Divides the result set into partitions (groups) to apply the function. If omitted, the function treats the entire result set as a single partition. * ORDER BY: Specifies the logical order of the rows within each partition.

How to Use ROW_NUMBER()

The ROW_NUMBER() function assigns a unique, sequential integer to each row within a partition, starting at 1 for the first row of each partition. If two rows have identical values in the ordered column, ROW_NUMBER() will still assign them different, sequential numbers.

Syntax

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression, ...]
    ORDER BY sort_expression [ASC | DESC], ...
)

Example

Suppose you have an employees table and want to assign a unique sequential number to each employee within their respective department, ordered by their salary from highest to lowest.

SELECT 
    employee_id, 
    department_id, 
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS row_num
FROM employees;

How to Use DENSE_RANK()

The DENSE_RANK() function assigns a rank to each row within a partition based on the order defined in the OVER clause. Identical values receive the same rank. Unlike the standard RANK() function, DENSE_RANK() does not skip any ranks when there are duplicate values, meaning the ranking sequence is always consecutive (e.g., 1, 2, 2, 3).

Syntax

DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ...]
    ORDER BY sort_expression [ASC | DESC], ...
)

Example

Using the same employees table, if you want to rank employees by their salary within each department but ensure that employees with the same salary share the same rank without leaving gaps in the ranking order, you use DENSE_RANK().

SELECT 
    employee_id, 
    department_id, 
    salary,
    DENSE_RANK() OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS dense_rnk
FROM employees;

The Difference Between ROW_NUMBER() and DENSE_RANK()

To illustrate the practical difference between these two functions, consider a dataset containing three employees in the same department with the same salary:

Employee ID Salary ROW_NUMBER() Result DENSE_RANK() Result
101 $5,000 1 1
102 $5,000 2 1
103 $4,500 3 2