How to Use the RANK() Function in MySQL

08/05/2021

Contents

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

Using the RANK() function in MySQL

The RANK() function in MySQL is used to calculate the rank of each row within a result set based on a specified column or set of columns. The rank value represents the position of the row within the result set, with ties receiving the same rank value.

Syntax

RANK() OVER (ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...)

Examples

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

Basic usage

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

In this example, the RANK() function is applied to the result set, ordered by the values of “column1” in ascending order. The result set will contain a new column named “rank” that will assign a rank to each row based on its position within the ordered result set.

Handling ties

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

In this example, the RANK() function is applied to the result set, ordered by the values of “column1” in descending order. In case of ties, the rows will receive the same rank value. For example, if two rows have the same value for “column1” and are ranked 2nd and 3rd, they will both receive a rank value of 2.

Ranking with partitioning

SELECT column1, column2, RANK() OVER (PARTITION BY column3 ORDER BY column1) AS rank
FROM table_name;

In this example, the RANK() function is applied to the result set, partitioned by the values of “column3” and ordered by the values of “column1” within each partition. The result set will contain a new column named “rank” that will assign a rank to each row within each partition based on its position within the ordered result set.

Ranking with ties and gaps

SELECT column1, column2, DENSE_RANK() OVER (ORDER BY column1) AS dense_rank,
       ROW_NUMBER() OVER (ORDER BY column1) AS row_number
FROM table_name;

In this example, the DENSE_RANK() and ROW_NUMBER() functions are used in addition to the RANK() function to create different ranking schemes. The DENSE_RANK() function handles ties by assigning the same rank value to each tied row and leaving gaps for the next rank value. The ROW_NUMBER() function assigns a unique sequential number to each row, without regard to ties.

#MySQL