What is the Difference Between RANK and DENSE_RANK in SQL?

RANK and DENSE_RANK are SQL window functions used to assign rankings to rows based on sorting conditions.

In simple words:

RANK skips ranking numbers when duplicate values exist
DENSE_RANK does not skip ranking numbers

Why RANK and DENSE_RANK are Important

Enterprise applications frequently require:

Leaderboard generation
Student rankings
Sales performance reports
Employee performance analysis
Top-N analytics

RANK and DENSE_RANK help:

Assign ordered positions to rows
Handle duplicate values properly
Perform advanced analytics

Main Difference Between RANK and DENSE_RANK

Feature	RANK	DENSE_RANK
Duplicate Values	Same rank assigned	Same rank assigned
Gap in Ranking	Yes	No
Ranking Sequence	Can skip numbers	Continuous ranking
Use Case	Competition ranking	Compact ranking

What is RANK?

RANK assigns:

Same rank to duplicate values and skips next ranking numbers.

RANK Syntax

SELECT column_name,

RANK() OVER (
    ORDER BY column_name DESC
) AS rank_value

FROM table_name;

Example Data

Student	Marks
Naresh	95
Rahul	90
Arjun	90
David	85

RANK Query

SELECT student_name,
       marks,

RANK() OVER (
    ORDER BY marks DESC
) AS student_rank

FROM students;

RANK Result

Student	Marks	RANK
Naresh	95	1
Rahul	90	2
Arjun	90	2
David	85	4

Important Observation

Rank:

is skipped.

Why Rank 3 Skipped?

Because:

Two students shared rank 2

RANK Internal Architecture

Sort Rows
    |
    v
Compare Current Row with Previous Row
    |
    v
Duplicate Value?
   /      \
 Yes       No
  |         |
Same Rank   New Rank
  |
  v
Skip Next Rank Number

What is DENSE_RANK?

DENSE_RANK assigns:

Same rank to duplicate values but does NOT skip ranking numbers.

DENSE_RANK Syntax

SELECT column_name,

DENSE_RANK() OVER (
    ORDER BY column_name DESC
) AS dense_rank_value

FROM table_name;

DENSE_RANK Query

SELECT student_name,
       marks,

DENSE_RANK() OVER (
    ORDER BY marks DESC
) AS student_rank

FROM students;

DENSE_RANK Result

Student	Marks	DENSE_RANK
Naresh	95	1
Rahul	90	2
Arjun	90	2
David	85	3

Important Observation

No rank is skipped.

DENSE_RANK Internal Architecture

Sort Rows
    |
    v
Compare Current Row with Previous Row
    |
    v
Duplicate Value?
   /      \
 Yes       No
  |         |
Same Rank   Increment Rank by 1

Visual Difference Between RANK and DENSE_RANK

RANK

95 → Rank 1
90 → Rank 2
90 → Rank 2
85 → Rank 4

DENSE_RANK

95 → Rank 1
90 → Rank 2
90 → Rank 2
85 → Rank 3

Why RANK Skips Numbers?

RANK behaves like:

Competition ranking

Example

Olympic race:

Two people get silver medal
Next person gets fourth position

Why DENSE_RANK Does Not Skip?

DENSE_RANK creates:

Continuous ranking sequence

Partition By Example

Ranking department-wise employees.

RANK with PARTITION BY

SELECT employee_name,
       department,
       salary,

RANK() OVER (

    PARTITION BY department
    ORDER BY salary DESC

) AS dept_rank

FROM employees;

What Happens?

Ranking restarts:

For each department

DENSE_RANK with PARTITION BY

SELECT employee_name,
       department,
       salary,

DENSE_RANK() OVER (

    PARTITION BY department
    ORDER BY salary DESC

) AS dept_rank

FROM employees;

Difference Between ROW_NUMBER, RANK, and DENSE_RANK

Function	Duplicates Allowed	Skips Numbers
ROW_NUMBER	No	No
RANK	Yes	Yes
DENSE_RANK	Yes	No

ROW_NUMBER Example

When to Use RANK

Competition ranking systems
Sports leaderboards
Exam rankings

When to Use DENSE_RANK

Continuous ranking systems
Analytics dashboards
Business reports

Performance Consideration

Both functions require:

Sorting operations

which may:

Increase execution cost on large datasets

Real-Time Banking Example

Banking systems use ranking functions for:

Top customers by balance
Credit score rankings
Branch performance reports

Example

Find top 5 customers in each branch.

Real-Time E-Commerce Example

E-commerce platforms use ranking functions for:

Top-selling products
Best-performing sellers
Customer loyalty rankings

Example

Rank products by monthly sales.

Real-Time Learning Platform Example

Learning platforms use ranking functions for:

Student leaderboards
Course performance analytics
Department-wise toppers

Example

Find top-ranked students per course.

Ranking Functions in Microservices

Microservices use ranking functions for:

Analytics APIs
Dashboard reporting
Recommendation systems

Advantages of RANK

Supports competition ranking
Reflects actual positional gaps

Advantages of DENSE_RANK

Continuous ranking
Compact analytical results

Disadvantages of RANK

Skipped ranks may confuse reporting

Disadvantages of DENSE_RANK

Does not reflect competition-style gaps

Best Practices

Use RANK for competition-style systems
Use DENSE_RANK for analytical reports
Use indexes for large sorting operations
Combine with PARTITION BY for grouped rankings

Common Interview Mistake

Many developers think:

RANK and DENSE_RANK behave identically

Reality

The major difference is:

Skipped ranking numbers

Professional Interview Answer

RANK and DENSE_RANK are SQL window functions used to assign rankings to rows based on sorting conditions. Both functions assign the same rank to duplicate values, but the difference is that RANK skips subsequent ranking numbers when duplicates exist, whereas DENSE_RANK does not skip numbers and maintains continuous ranking. RANK is commonly used in competition-style ranking systems, while DENSE_RANK is preferred for analytical reports and continuous ranking scenarios. These functions are widely used in enterprise applications for leaderboards, sales analytics, reporting dashboards, and performance analysis.

Why Interviewers Like This Answer

Clearly explains ranking behavior
Includes duplicate handling concepts
Shows window function knowledge
Provides real-world examples
Explains reporting and analytics use cases

Frequently Asked Questions