---
title: "When should you use ROW_NUMBER(), RANK(), or DENSE_RANK() in a query?"  
description: "When should you use ROW_NUMBER(), RANK(), or DENSE_RANK() in a query?"  
author: "Ashutosh Patel"  
published: 2025-04-30  
updated: 2026-05-16  
canonical: https://www.mindstick.com/forum/161548/when-should-you-use-row_number-rank-or-dense_rank-in-a-query  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql", "SQL Database"]  
reading_time: 4 minutes  

---

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

When should you use ROW_NUMBER(), [RANK](https://www.mindstick.com/forum/33707/what-is-the-difference-between-rank-and-dense_rank-functions-in-sql-server)(), or DENSE_RANK() in a [query](https://www.mindstick.com/blog/202/sub-query-in-sqlserver)?

## Replies

### Reply by ICSM Computer

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

```plaintext
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

```plaintext
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

```plaintext
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

```plaintext
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

```plaintext
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`.

```plaintext
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

```plaintext
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.


---

Original Source: https://www.mindstick.com/forum/161548/when-should-you-use-row_number-rank-or-dense_rank-in-a-query

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.