Using MySQL Dense_Rank() Function

The MySQL DENSE_RANK () function is a function that displays the rank of a row in its partition without gaps in the ranking values. This function is a window function, which means it has similarities to functions such as ROW_NUMBER() and RANK() functions.

This tutorial shall delve deeper into how the MySQL dense function works and how we can use it in various database operations. If you want to follow along with this tutorial, consider downloading the Sakila sample database from the resource provided below:

https://dev.mysql.com/doc/index-other.html

Basic Usage

MySQL DENSE_RANK() function displays the ranks of a row within a partition with no gaps. The ranks of the rows increase by a value of 1 from the unique rank value of the previous row.

The general syntax of the DENSE_RANK() function is as:

SELECT col1 DENSE_RANK() OVER (PARTITION BY {expr} ORDER BY {expr} [ASC|DESC] ) rank_col_name FROM tb_name

Let us examine the above query syntax more closely.

The DENSE_RANK() function ties to the SELECT clause, which will display the ranks of the rows from the table specified.

The DENSE_RANK() Over sections return the result of DENSE_RANK() function and the output held in the column name specified.

The partition by clause divides the results returned by the FROM clause into partitions. The DENSE_RANK() function is applied to each partition.

Finally, the ORDER BY section specifies the order of the set rows in each partition.

Example Use Case

Let’s use a sample database to illustrate how we can use the DENSE_RANK() function. For this example, we shall use the Sakila database and, more specifically, the film table in the Sakila database.

Using the DENSE_RANK() function, we can rank the movies by their rental rate, as shown in the query below:

USE sakila;
SELECT title, release_year, rating, length, DENSE_RANK() OVER (PARTITION BY release_year ORDER BY rental_rate ASC) rank_value FROM film;

Due to the massive amount of data on the Sakila database, I will re-organize the output for easier reading and illustration.

The output is below:

If you look carefully at the output above, you will notice that the resulting output ranges from rank 1 to 3, which corresponds to the rental_rate values in the film table. The rental_rate values are:

0.99 – rank 1
2.99 – rank 2
4.99 – rank 3

In the above example, we used the partition by clause to divide the resulting sets into various partitions, in this case, release_year.

Next, we used the MySQL order by the statement to order the films by the rental rate in ascending order. Finally, we applied the DENSE_RANK() function on each partition specified in the order by statement.

Conclusion

In this tutorial, we explained how the DENSE_RANK() function works in a database and used a real-world example to illustrate how to use it.

You can learn more about DENSE_RANK() ad other window functions from the resource provided below:

https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

Using MySQL Dense_Rank() Function

Basic Usage

Example Use Case

Conclusion

About the author

John Otieno