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, 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.

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 BY clause.
  • The 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.

Examples

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_rank
FROM Students;
RANK() and DENSE_RANK() in PostgreSQL

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_rank
FROM Students;
RANK() and DENSE_RANK() in MySQL

As we explained earlier, we can see the different rank assignments in the output.

We can also use the RANK() and DENSE_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 City
ORDER BY Marks DESC) AS _rank,
DENSE_RANK() OVER(
PARTITION BY City
ORDER BY Marks DESC) AS _dense_rank
FROM Students;
RANK() and DENSE_RANK() with PARTITION BY clause in PostgreSQL

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 City
ORDER BY Marks DESC) _rank,
DENSE_RANK() OVER(
PARTITION BY City
ORDER BY Marks DESC) _dense_rank
FROM Students;
RANK() and DENSE_RANK() with PARTITION BY clause in MySQL

There are two same marks values in Lahore city. We can see the rank difference in the output of the above playgrounds.

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved