When Do You Use the DENSE_RANK() Function in MySQL?

MySQL is one of the scripting languages used today in the web development platform. This article is aimed at offering you with ways of answering short interview questions regarding MySQL, particularly the DENSE_RANK function. DENSE_RANK is one of the fundamental analytic ranking functions in MySQL. Find more about it in the section below.

How is the DENSE_RANK() Function Used?

dense Rank function in my SQL

Establishing a DENSE_RANK() SQL function within a data set is a standard requirement when displaying a set of results. You can use it both in SQL Server and MySQL starting from the 2005 version. The DENSERANK function usually returns the serial or ID number for a given set of values while keeping a similar number if the value is duplicated.

The dense_rank function works the same way as the row_number but in a special way. The only point dividing the two is that in DENSE_RANK(), the rows that have the same values are also ranked but given the same number. In other words with this function there are no gaps; hence it is dense.

If there is a tie in a set of records in a table, the DENSE_RANK() function will not skip any of them. Instead, the values will receive a similar position while maintaining the rank in a consecutive order. Take an example of these values: {10, 10, 20, 30, 30, and 50}. When the SQL DENSERANK() is applied, the column of the rankings will show {1, 1, 2, 3, 3, 4} respectively. The tie doesn’t affect the next consecutive integer value hence none is skipped.

MySQL supports the use of SQL variables in the SQL queries. We can then use the variable support to generate the DENSE_RANK() function values.

Example

Consider this MySQL set of data:

create table Student_Details(names varchar(100))
insert into Student_detailstest
select ‘tom’ union all
select ‘Dawson’ union all
select ‘Kent’ union all
select ‘Gerald’ union all
select ‘Tim’ union all
select ‘Dawson’ union all
select ‘Kent’ union all
select ‘Gerald’ union all
select ‘Johnny’

You can use a variable to return the ID number as well as another variable to keep similar values for duplicates. The SQL query for a dense_rank function dedicated to generating the ID number and resetting each record would appear as follows:

Select dense_rank() over (OrderBy names) as ID_no,names from Student_details

The query will rank the students bringing out the results below:

Tom, Dawson, Dawson, Tim, Kent, Kent, Gerald, Gerald, Johnny under the names column, followed by their respective ID_no 1,2,2,3,4,4,5,5,6 in that particular order.

The SQL DENSE_RANK() function can be used in several other ways such as an aggregate function and as an analytic function. As an aggregate function, dense_rank will give you a dense rank of a row situated among a set of rows. Its syntax is:

DENSE_RANK( 1st expression, … nth expression) WITHIN GROUP ( ORDER BY 1st expression, … nth expression)

However, when using it as an aggregate function, the number of expressions in the 1st Expression should be equal to the ones in the order by clause.

After reading this article, you are well equipped to tackle your IT interview. Share your thoughts or comments on the use of SQL dense rank function.