MySQL DENSE_RANK() Function – An Easy Reference

Dense Rank() In MySQL

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


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>...}]

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),

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

    Dense_Rank() Over (
    ORDER BY number
    ) rank_list


MySQL Dense_Rank
Dense Rank

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)
    ('Peter', 2018, 455),
    ('David', 2018, 555),
    ('John', 2017, 666),
    ('Peter', 2017, 332),
    ('David', 2017, 678),
    ('Lily', 2018, 569),
    ('John', 2018, 982),
    ('Lily', 2017, 666);

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

    Dense_Rank() Over (
    Partition By
    Order By
    ) sale_rank


MySQL Dense_Rank
Dense Rank

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.


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.