How to Use the ROW_NUMBER() Function in MySQL

08/05/2021

Contents

In this article, you will learn how to use the ROW_NUMBER() function in MySQL.

Using the ROW_NUMBER() function in MySQL

The ROW_NUMBER() function in MySQL is used to assign a unique sequential number to each row within a result set. This function is commonly used for pagination, ranking, and identifying duplicate rows.

Syntax

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

Examples

Here are some explanations and examples of how to use the ROW_NUMBER() function in MySQL:

Basic usage

SELECT ROW_NUMBER() OVER () AS row_number, column1, column2
FROM table_name;

In this example, the ROW_NUMBER() function is applied to the entire result set, without any partition or order criteria. The result set will contain a new column named “row_number” that will assign a unique sequential number to each row.

Partitioning

SELECT ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2 DESC) AS row_number, column1, column2
FROM table_name;

In this example, the ROW_NUMBER() function is partitioned by the values of “column1”, and then ordered by “column2” in descending order within each partition. The result set will contain a new column named “row_number” that will assign a unique sequential number to each row within each partition.

Ranking

SELECT ROW_NUMBER() OVER (ORDER BY column1 DESC) AS rank, column1, column2
FROM table_name;

In this example, the ROW_NUMBER() function is used to create a rank for each row based on the values of “column1”, in descending order. The result set will contain a new column named “rank” that will assign a sequential number to each row based on its ranking.

Identifying duplicates

SELECT ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY column3) AS row_number, column1, column2, column3
FROM table_name;

In this example, the ROW_NUMBER() function is used to identify duplicate rows based on the values of “column1” and “column2”, and then ordered by “column3”. The result set will contain a new column named “row_number” that will assign a unique sequential number to each row within each set of duplicates.

Pagination

SELECT column1, column2
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY column1) AS row_number, column1, column2
    FROM table_name
) AS t
WHERE t.row_number BETWEEN 11 AND 20;

In this example, the ROW_NUMBER() function is used to create a sequential number for each row based on the values of “column1”, and then the results are filtered to only include rows 11-20, effectively creating a pagination system.

#MySQL