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, the RANK()
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, the DENSE_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
.
The syntax of RANK()
and DENSE_RANK()
functions follow the same pattern:
OVER()
clause.ORDER BY
clause.PARTITION BY
clause is optional that is used to rank within different partitions.The syntax of the RANK()
is as follows:
We can replace RANK()
with DENSE_RANK()
in the above syntax for a DENSE_RANK()
query.
Consider the following table for the examples:
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.
PARTITION BY
clauseLet’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