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


dense Rank function in my SQL

*This post may contain affiliate links. As an Amazon Associate we earn from qualifying purchases.

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. This function 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 dense_rank 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.

SQL DENSE_RANK 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.

The Difference between RANK and DENSE_RANK Functions

Unlike the RANK function that returns the ranking within an ordered partition, assigns the same rank to ties, and skips the next one(s), the DENSE_RANK function consecutively orders the ranks in an ordered partition without skipping ranks with multiple items.

Other recommended reads: If you want to go through more interview questions & answers that might help you during an SQL interview, we also recommend going through our collection of SQL interview questions and?our articles on SQL injection attack, the SQL ORDER BY clause, and the SQL UNIQUE constraint.

Recent Posts