What is difference between ROW_NUMBER() vs RANK() vs DENSE_RANK()?

ROW_NUMBER(), RANK(), and DENSE_RANK() are SQL window functions used for ranking rows.

The main difference is how they handle ties (duplicate values).

1. ROW_NUMBER()

Assigns a unique number to every row. Even if two rows have the same value, they get different numbers.

Example

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

Data

Output

No ties allowed.

2. RANK()

Rows with the same value get the same rank. But skipped ranks appear after ties.

Example

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank_num
FROM employees;

Output

Notice:

Rank 3 is skipped.

3. DENSE_RANK()

Same values get the same rank, but no gaps in ranking.

Example

SELECT name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;

Output

No skipped ranks.

Quick Comparison

Easy Memory Trick

Suppose marks are:

100, 90, 90, 80

Common Use Cases

• ROW_NUMBER() → pagination, unique ordering
• RANK() → competition ranking (1st, 2nd, 2nd, 4th)
• DENSE_RANK() → leaderboard without gaps