# 
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:



SELECT column_names,
  RANK() OVER(
    PARTITION BY expression
    ORDER BY expression [ASC/DESC]
  ) rank_column_name
FROM table name;

MySQL

SELECT column_names,
  RANK() OVER(
    PARTITION BY expression
    ORDER BY expression [ASC/DESC]
  ) AS rank_column_name
FROM table name;

PostgreSQL

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

align-center

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;

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;

mysql-code

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;

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;

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

mysql.tar.gz

Difference between RANK() and DENSE_RANK() in PostgreSQL/MySQL

RANK() gaps in sequence after duplicates; DENSE_RANK() continues without gaps.

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

Difference between RANK() and DENSE_RANK() in PostgreSQL/MySQL

Syntax

Examples

Students

Code examples with the PARTITION BY clause

Code examples with the `PARTITION BY` clause