How to Use the DENSE_RANK() Function in MySQL

08/05/2021

Contents

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

Using the DENSE_RANK() function in MySQL

The DENSE_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. However, unlike the RANK() function, the DENSE_RANK() function does not leave gaps in the ranking sequence when there are ties.

Syntax

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

Examples

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

Basic usage

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

In this example, the DENSE_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 “dense_rank” that will assign a rank to each row based on its position within the ordered result set, with ties receiving the same rank value without leaving gaps in the ranking sequence.

Handling ties

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

In this example, the DENSE_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 without leaving gaps in the ranking sequence.

Ranking with partitioning

SELECT column1, column2, DENSE_RANK() OVER (PARTITION BY column3 ORDER BY column1) AS dense_rank
FROM table_name;

In this example, the DENSE_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 “dense_rank” that will assign a rank to each row within each partition based on its position within the ordered result set, with ties receiving the same rank value without leaving gaps in the ranking sequence.

Ranking with ties and gaps

SELECT column1, column2, RANK() OVER (ORDER BY column1) AS rank,
       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() function is used in addition to the RANK() and ROW_NUMBER() functions to create different ranking schemes. The RANK() function assigns the same rank value to tied rows and leaves gaps for the next rank value, while the DENSE_RANK() function assigns the same rank value to tied rows and does not leave gaps in the ranking sequence. The ROW_NUMBER() function assigns a unique sequential number to each row, without regard to ties.

#MySQL