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 function such as ROW_NUMBER() and RANK() function.
Before we get started, if you want to follow along with this tutorial, consider downloading the Sakila sample database from the resource provided below:
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_nameLet us examine the above query syntax more closely.
- The
Dense_Rank()function ties to theSELECTclause, which will display the ranks of the rows from the table specified. Dense_rank()Over sections returns the result ofdense_rank()function and the output held in the column name specified.- The partition by clause divides the results returned by the
FROMclause into partitions where thedense_rank()function is applied to each partition. - Finally, the
ORDER BYsection 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;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 statement to order the films by the rental rate in ascending order, and finally, we applied the dense_rank() function on each partition as 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() and other window functions from the resource provided below:
