12.2 RANK() and DENSE_RANK()

Level: 12 | Version: v1.1 | Author: Meptrasoft

Overview

RANK() and DENSE_RANK() assign a rank to rows based on an ORDER BY.

The main difference is how they handle ties.

Function	Tie Handling
`RANK()`	Same rank, then skips the next rank
`DENSE_RANK()`	Same rank, but does not skip

RANK → Tie + Gap DENSE_RANK → Tie + No Gap

RANK() vs DENSE_RANK() — Handling Ties With and Without Gaps — Figure 1: RANK() leaves gaps after ties, while DENSE_RANK() keeps the ranking sequence continuous.

Practice Table

This topic reuses the employee table from 12.1.

name	department	salary
Andrew	Finance	50000
Brian	Finance	50000
Charles	Finance	20000
Daniel	Sales	30000
Ethan	Sales	20000

RANK()

RANK() gives the same rank to tied rows, but skips rank numbers after the tie.

SELECT
    name,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS emp_rank
FROM employee;

Finance:

name	salary	rank
Andrew	50000	1
Brian	50000	1
Charles	20000	3

Andrew and Brian tie at 1, so the next rank is 3.

RANK() → Ties create gaps.

DENSE_RANK()

DENSE_RANK() also gives the same rank to tied rows, but does not skip the next rank.

SELECT
    name,
    department,
    salary,
    DENSE_RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS emp_dense_rank
FROM employee;

Finance:

name	salary	dense_rank
Andrew	50000	1
Brian	50000	1
Charles	20000	2

DENSE_RANK() → Ties do not create gaps.

RANK vs DENSE_RANK

Salary: 50000, 50000, 20000

RANK()
→ 1, 1, 3

DENSE_RANK()
→ 1, 1, 2

Easy Memory Trick

RANK       → GAP
DENSE_RANK → NO GAP

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
`RANK()` gives every row a unique number	Tied rows receive the same rank
`DENSE_RANK()` skips after ties	It does not skip
Both functions always return `1, 2, 3...`	Ties can change the sequence
`PARTITION BY` is required	It is optional
Ranking changes the stored table	It only adds a calculated value to the query result

Placement Quick Points

RANK()
→ SAME RANK FOR TIES
→ GAPS AFTER TIES

DENSE_RANK()
→ SAME RANK FOR TIES
→ NO GAPS

Both functions are window functions.
Both assign ranks according to the ORDER BY.
RANK() leaves gaps after ties.
DENSE_RANK() does not leave gaps.
PARTITION BY can create separate rankings for each group.

Interview Questions

What is RANK()?

RANK() assigns ranks to rows and gives tied rows the same rank, leaving gaps after ties.

What is DENSE_RANK()?

DENSE_RANK() assigns the same rank to tied rows without leaving gaps.

What is the main difference?

RANK       → 1, 1, 3
DENSE_RANK → 1, 1, 2

Can rankings be calculated separately by department?

Yes, using:

PARTITION BY department

Does RANK() modify the table?

No. It calculates the rank in the query result.

Practice & Hands-On Exercises

Using the employee table:

Rank employees by salary within each department.
Apply RANK():

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS emp_rank
FROM employee;

Apply DENSE_RANK():

SELECT name, department, salary,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS emp_dense_rank
FROM employee;

Compare the results when salaries are tied.
Explain why RANK() produces a gap.
Explain why DENSE_RANK() does not.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

RANK()
→ 1, 1, 3

DENSE_RANK()
→ 1, 1, 2

Both functions handle ties with the same rank, but RANK() leaves gaps while DENSE_RANK() keeps the ranking continuous.

End of RANK() and DENSE_RANK()