Difference between RANK() and DENSE_RANK() in PostgreSQL/MySQL
The RANK() and DENSE_RANK() functions are used to rank each row within a result set. The ranks are assigned in a sequence. Both functions assign the same rank to the same elements. The difference lies in the rank assignment after the same elements. We can differentiate as follows:
RANK(): If two or more elements are the same, theRANK()function will allocate them the same value and the next rank value will be the current rank plus the duplicated items.DENSE_RANK(): If two or more elements are the same, theDENSE_RANK()function will allocate them the same value and the next rank value will be the next consecutive number.
Let’s understand from the following illustration:
In this illustration, you can see the rank assignment of the RANK() and the DENSE_RANK() functions. Consider the first two same values 95, and rank value 2 is assigned to both values. The RANK() function will assign a rank value of 4 and the DENSE_RANK() will assign a rank value of 3. The same is applied to the second same value 80.
Syntax
The syntax of RANK() and DENSE_RANK() functions follow the same pattern:
- They are used with the
OVER()clause. - The rank is allocated on the basis of the
ORDER BYclause. - The
PARTITION BYclause is optional that is used to rank within different partitions.
The syntax of the RANK() is as follows:
SELECT column_names,RANK() OVER(PARTITION BY expressionORDER BY expression [ASC/DESC]) rank_column_nameFROM table name;
We can replace RANK() with DENSE_RANK() in the above syntax for a DENSE_RANK() query.
Examples
Consider the following table for the examples:
Students
ID | Name | Gender | City | Marks |
1 | Ali | Male | Lahore | 90 |
2 | Basit | Male | Okara | 100 |
3 | Sana | Female | Lahore | 70 |
4 | Dua | Female | Lahore | 95 |
5 | Raza | Male | Lahore | 95 |
6 | Saba | Female | Karachi | 80 |
7 | Riaz | Male | Karachi | 85 |
8 | Eman | Female | Lahore | 80 |
Let’s run the RANK() and DENSE_RANK() to rank students based on marks in the PostgreSQL database.
SELECT ID, Name, Marks,RANK() OVER(ORDER BY Marks DESC) AS _rank,DENSE_RANK() OVER(ORDER BY Marks DESC) AS _dense_rankFROM Students;
Let’s run the RANK() and DENSE_RANK() to rank students based on marks in the MySQL database.
SELECT ID, Name, Marks,RANK() OVER (ORDER BY Marks DESC) _rank,DENSE_RANK() OVER(ORDER BY Marks DESC) _dense_rankFROM Students;
As we explained earlier, we can see the different rank assignments in the output.
We can also use the
RANK()andDENSE_RANK()functions on text-based columns, i.e., City, etc.
Code examples with the PARTITION BY clause
Let’s use the RANK() and DENSE_RANK() functions with the PARTITION BY clause in PostgreSQL to rank the students in each city.
SELECT ID, Name, City, Marks,RANK() OVER(PARTITION BY CityORDER BY Marks DESC) AS _rank,DENSE_RANK() OVER(PARTITION BY CityORDER BY Marks DESC) AS _dense_rankFROM Students;
Let’s use the RANK() and DENSE_RANK() functions with the PARTITION BY clause in MySQL to rank the students in each city.
SELECT ID, Name, City, Marks,RANK() OVER(PARTITION BY CityORDER BY Marks DESC) _rank,DENSE_RANK() OVER(PARTITION BY CityORDER BY Marks DESC) _dense_rankFROM Students;
There are two same marks values in Lahore city. We can see the rank difference in the output of the above playgrounds.
Free Resources