Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

home / developersection / forums / when should you use row_number(), rank(), or dense_rank() in a query?
Ask Question
Ashutosh Patel
Ashutosh Patel

Web Developer

I am a professional .NET developer with over 4 years of hands-on industry experience in designing, developing, and maintaining scalable web applications. I specialize in .NET Core, C#, RESTful APIs, and database-driven systems using SQL Server.

Message

1 Answers

Ravi Vishwakarma
Ravi Vishwakarma 16 May 2026
Report Abuse

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.

advertising