RANK, DENSE_RANK

In SQL, ranking functions allow you to assign a rank to each row within a result set based on the values in one or more columns. These are commonly used to find top-performing items, order data by score, or identify relative positions. Two of the most frequently used ranking functions are RANK and DENSE_RANK.

---

1. RANK Function

The RANK function assigns a unique rank to each distinct value within a result set. If multiple rows have the same value, they receive the same rank. However, the next rank will skip values accordingly.

Syntax:

RANK() OVER (
    [PARTITION BY partition_column]
    ORDER BY order_column [ASC | DESC]
)

Explanation:

• PARTITION BY: Divides the data into groups and ranks each group separately. Optional.
• ORDER BY: Specifies the column(s) to rank by. Mandatory.
• Rows with identical values get the same rank.
• Ranks are not consecutive if ties occur.

Example:

Suppose we have a table of students with their scores:

Student   Score
Alice     95
Bob       95
Charlie   90
David     85

Query:

SELECT
    Student,
    Score,
    RANK() OVER (ORDER BY Score DESC) AS Rank
FROM Students;

Result:

Student   Score   Rank
Alice     95      1
Bob       95      1
Charlie   90      3
David     85      4

Notice that the rank jumps from 1 to 3 after the tie.

---

2. DENSE_RANK Function

The DENSE_RANK function works similarly to RANK but does not leave gaps between ranks when there are ties. Rows with the same value still share the same rank, but the next rank is consecutive.

Syntax:

DENSE_RANK() OVER (
    [PARTITION BY partition_column]
    ORDER BY order_column [ASC | DESC]
)

Explanation:

• Like RANK, you can partition data.
• Identical values get the same rank.
• Ranks are consecutive, even after ties.

Example:

Using the same table:

SELECT
    Student,
    Score,
    DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank
FROM Students;

Result:

Student   Score   DenseRank
Alice     95      1
Bob       95      1
Charlie   90      2
David     85      3

Notice how the ranks are consecutive, unlike RANK.

---

Key Differences

• RANK: Leaves gaps in ranking when ties occur.
• DENSE_RANK: No gaps; ranks are consecutive.
• Both can use PARTITION BY to rank within groups.

Example with Partition:

Suppose we have scores by class:

Class   Student   Score
A       Alice     95
A       Bob       95
A       Charlie   90
B       David     85
B       Eve       80

Query with partition:

SELECT
    Class,
    Student,
    Score,
    RANK() OVER (PARTITION BY Class ORDER BY Score DESC) AS Rank,
    DENSE_RANK() OVER (PARTITION BY Class ORDER BY Score DESC) AS DenseRank
FROM Students;

Result:

Class   Student   Score   Rank   DenseRank
A       Alice     95      1      1
A       Bob       95      1      1
A       Charlie   90      3      2
B       David     85      1      1
B       Eve       80      2      2

---

Summary

• Use RANK when you want the actual ranking positions, including gaps after ties.
• Use DENSE_RANK when you want consecutive rankings, ignoring gaps caused by ties.
• Both functions are helpful for leaderboards, top-n queries, and group-based ranking.