When should you use ROW_NUMBER(), RANK(), or DENSE_RANK() in a query?

Question

ICSM Computer · Answer

In Microsoft SQL Server and SQL generally, ROW_NUMBER(), RANK(), and DENSE_RANK() are window functions used for ranking rows within a result set.

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

1. ROW_NUMBER()

Use ROW_NUMBER() when:

You want a unique sequential number for every row
Even duplicate values should get different numbers
You need pagination
You want to pick one row from duplicates

Example

SELECT
    EmployeeName,
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;

Result

Employee	Salary	RowNum
John	10000	1
Mike	9000	2
Sarah	9000	3
Alex	8000	4

Even though Mike and Sarah have the same salary, they get different row numbers.

Common Use Cases

Pagination

WITH EmployeeCTE AS
(
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY Id) AS RowNum
    FROM Employees
)
SELECT *
FROM EmployeeCTE
WHERE RowNum BETWEEN 11 AND 20;

Remove Duplicate Records

WITH CTE AS
(
    SELECT *,
           ROW_NUMBER() OVER
           (
               PARTITION BY Email
               ORDER BY Id
           ) AS RN
    FROM Users
)
DELETE FROM CTE
WHERE RN > 1;

2. RANK()

Use RANK() when:

You want same rank for tied values
Gaps in ranking are acceptable
You need competition-style ranking

Example

SELECT
    EmployeeName,
    Salary,
    RANK() OVER (ORDER BY Salary DESC) AS RankNum
FROM Employees;

Result

Employee	Salary	Rank
John	10000	1
Mike	9000	2
Sarah	9000	2
Alex	8000	4

Notice:

Rank 3 is skipped
Because two rows share rank 2
This is called "gapped ranking."

Common Use Cases

Leaderboards
Tournament rankings
Sales rankings
Academic rankings
Where ties should affect the next position.

3. DENSE_RANK()

Use DENSE_RANK() when:

You want same rank for duplicates
But you do NOT want gaps in ranking

Example

SELECT
    EmployeeName,
    Salary,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRankNum
FROM Employees;

Result

Employee	Salary	Dense Rank
John	10000	1
Mike	9000	2
Sarah	9000	2
Alex	8000	3

Notice:

No skipped rank
Rank continues sequentially

Visual Comparison

Salary	ROW_NUMBER	RANK	DENSE_RANK
10000	1	1	1
9000	2	2	2
9000	3	2	2
8000	4	4	3

When to Use Which

Function	Best Use Case
ROW_NUMBER()	Unique row sequencing
RANK()	Competition ranking with gaps
DENSE_RANK()	Ranking without gaps

Partitioning Example

You can rank within groups using PARTITION BY.

SELECT
    Department,
    EmployeeName,
    Salary,
    RANK() OVER
    (
        PARTITION BY Department
        ORDER BY Salary DESC
    ) AS DeptRank
FROM Employees;

This ranks employees separately inside each department.

Real-World Examples

ROW_NUMBER()
- Pagination
- Deduplication
- Latest record selection
RANK()
- Sports rankings
- Exam scores
- Sales leaderboards
DENSE_RANK()
- Category rankings
- Tier systems
- Priority levels

Interview-Friendly Explanation

A simple way to remember:

Function	Duplicate Values
ROW_NUMBER()	Always unique
RANK()	Same rank + skips next rank
DENSE_RANK()	Same rank + no skipped ranks

Example Question

Find top 3 highest-paid employees per department

WITH RankedEmployees AS
(
    SELECT
        Department,
        EmployeeName,
        Salary,
        DENSE_RANK() OVER
        (
            PARTITION BY Department
            ORDER BY Salary DESC
        ) AS RankNum
    FROM Employees
)
SELECT *
FROM RankedEmployees
WHERE RankNum <= 3;

This is one of the most common real-world uses of ranking functions.

forum

When should you use ROW_NUMBER(), RANK(), or DENSE_RANK() in a query?

Can you answer this question?

1 Answers

1. ROW_NUMBER()

Example

Result

Common Use Cases

Pagination

Remove Duplicate Records

2. RANK()

Example

Result

Common Use Cases

3. DENSE_RANK()

Example

Result

Visual Comparison

When to Use Which

Partitioning Example

Real-World Examples

Interview-Friendly Explanation

Example Question

Find top 3 highest-paid employees per department