MySQL DENSE_RANK() Function – An Easy Reference

In this tutorial, we will study the MySQL Dense_Rank() function which is one of the window functions in MySQL.

Also read: MySQL Cume_Dist Function – A Complete Guide

Introduction

MySQL Dense_Rank() function is used to find the rank of a row in a result set and the rank is increased by one depending on the previous row ranks.

Syntax of MySQL DENSE_RANK()

Dense_Rank() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)
Code language: SQL (Structured Query Language) (sql)

Here, the Partition By clause is used to divide the result set and the Order By clause is used specify the order of the rows. If a partition has more than one row with the same rank value then those rows will be assigned same rank.

Example of MySQL DENSE_RANK()

Let us create a table named abc. The code for this is-

Create Table abc (
    number Int
    );
Insert Into abc(number)
    values (1),
    (2),
    (2),
    (3),
    (3),
    (4),
    (5),
    (5);Code language: SQL (Structured Query Language) (sql)

Now we will apply MySQL Dense_Rank() to this table. The code for this is-

Select
    number,
    Dense_Rank() Over (
    ORDER BY number
    ) rank_list
    From
    abc;Code language: SQL (Structured Query Language) (sql)

Output-

In the above example we applied DENSE_RANK()() function and obtained the output which is arranged rank-wise.

Now, we will again create a table sales and perform MySQL DENSE_RANK() function operation on it. The code for this is-

Create Table sales (
    name VARCHAR(300),
    year INT,
    sale INT
    );
Insert Into sales(name, year, sale)
    Values
    ('Peter', 2018, 455),
    ('David', 2018, 555),
    ('John', 2017, 666),
    ('Peter', 2017, 332),
    ('David', 2017, 678),
    ('Lily', 2018, 569),
    ('John', 2018, 982),
    ('Lily', 2017, 666);Code language: SQL (Structured Query Language) (sql)

Now let’s apply MySQL DENSE_RANK() function to this table. The code for this is-

Select
    name,
    year,
    sale,
    Dense_Rank() Over (
    Partition By
    year
    Order By
    sale
    ) sale_rank
    From
    sales;Code language: SQL (Structured Query Language) (sql)

Output-

Firstly, Partition By clause is applied to this table to divide the result set by year. Next the Order By clause sets the order of the table based on the sale which is by default ascending. Lastly, the Dense_Rank() function is applied to it and output is obtained.

Conclusion

In this tutorial, we studied the Dense_Rank function which is used to assign rank value to each row in a result set. For more references, check the official documentation of MySQL.