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:
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:
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:
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.
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: