SQL Topics
RANK, DENSERANK, and ROWNUMBER
title: RANK, DENSERANK, and ROWNUMBER
In the previous lesson, you learned about:
Window Functions
OVER()
PARTITION BY
ORDER BYOne of the most common uses of Window Functions is:
Ranking DataExamples:
Top Employees
Top Students
Top Selling Products
Highest Salaries
Leaderboard SystemsTo perform ranking operations, SQL provides:
ROW_NUMBER()
RANK()
DENSE_RANK()These functions assign numbers to rows based on sorting criteria.
Although they look similar, their behavior is different when duplicate values exist.
Understanding these differences is extremely important for SQL interviews and real-world reporting.
Why Do We Need Ranking Functions?
Suppose we have:
| Employee | Salary |
|---|---|
| Rahul | 80000 |
| Priya | 70000 |
| Amit | 70000 |
| Neha | 60000 |
Question:
Who Is 1st?
Who Is 2nd?
Who Is 3rd?Ranking functions solve this problem.
Ranking Function Categories
SQL provides:
ROW_NUMBER()
RANK()
DENSE_RANK()All are Window Functions and require:
OVER()ROW_NUMBER()
Assigns a unique sequential number to each row.
Even if duplicate values exist:
Every Row Gets Different NumberSyntax
ROW_NUMBER()
OVER
(
ORDER BY ColumnName
)Example Table
| Employee | Salary |
|---|---|
| Rahul | 80000 |
| Priya | 70000 |
| Amit | 70000 |
| Neha | 60000 |
Query
SELECT
EmployeeName,
Salary,
ROW_NUMBER()
OVER
(
ORDER BY Salary DESC
)
AS RowNum
FROM Employees;Output
| Employee | Salary | RowNum |
|---|---|---|
| Rahul | 80000 | 1 |
| Priya | 70000 | 2 |
| Amit | 70000 | 3 |
| Neha | 60000 | 4 |
Important Observation
Even though:
Priya
Amithave the same salary,
they receive different numbers.
Characteristics of ROW_NUMBER()
Always Unique
No Duplicate Ranks
No GapsRANK()
Assigns the same rank to duplicate values.
However:
Skips Rank Numbersafter duplicates.
Syntax
RANK()
OVER
(
ORDER BY ColumnName
)Query
SELECT
EmployeeName,
Salary,
RANK()
OVER
(
ORDER BY Salary DESC
)
AS EmployeeRank
FROM Employees;Output
| Employee | Salary | Rank |
|---|---|---|
| Rahul | 80000 | 1 |
| Priya | 70000 | 2 |
| Amit | 70000 | 2 |
| Neha | 60000 | 4 |
Important Observation
Notice:
Rank 3 Missingbecause:
Two Employees Share Rank 2Characteristics of RANK()
Same Values
Same Rank
Gaps ExistDENSE_RANK()
Assigns the same rank to duplicate values.
Unlike RANK():
No Rank Numbers Are SkippedSyntax
DENSE_RANK()
OVER
(
ORDER BY ColumnName
)Query
SELECT
EmployeeName,
Salary,
DENSE_RANK()
OVER
(
ORDER BY Salary DESC
)
AS DenseRank
FROM Employees;Output
| Employee | Salary | DenseRank |
|---|---|---|
| Rahul | 80000 | 1 |
| Priya | 70000 | 2 |
| Amit | 70000 | 2 |
| Neha | 60000 | 3 |
Important Observation
No gap exists.
Ranks:
1
2
2
3Comparing All Three
Query:
SELECT
EmployeeName,
Salary,
ROW_NUMBER()
OVER
(
ORDER BY Salary DESC
)
AS RowNumber,
RANK()
OVER
(
ORDER BY Salary DESC
)
AS RankNumber,
DENSE_RANK()
OVER
(
ORDER BY Salary DESC
)
AS DenseRankNumber
FROM Employees;Output
| Employee | Salary | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| Rahul | 80000 | 1 | 1 | 1 |
| Priya | 70000 | 2 | 2 | 2 |
| Amit | 70000 | 3 | 2 | 2 |
| Neha | 60000 | 4 | 4 | 3 |
Visual Comparison
Salary
80000 → 1 → 1 → 1
70000 → 2 → 2 → 2
70000 → 3 → 2 → 2
60000 → 4 → 4 → 3
↑ ↑
ROW RANK
DENSE_RANK Has No GapPARTITION BY with Ranking
Ranking can be applied within groups.
Example Table
| Employee | Department | Salary |
|---|---|---|
| Rahul | HR | 80000 |
| Priya | HR | 70000 |
| Amit | IT | 90000 |
| Neha | IT | 70000 |
Query
SELECT
EmployeeName,
Department,
Salary,
RANK()
OVER
(
PARTITION BY Department
ORDER BY Salary DESC
)
AS DepartmentRank
FROM Employees;Output
| Employee | Department | Rank |
|---|---|---|
| Rahul | HR | 1 |
| Priya | HR | 2 |
| Amit | IT | 1 |
| Neha | IT | 2 |
Top N Records Using ROW_NUMBER()
Get Top 3 Salaries:
WITH SalaryRanking AS
(
SELECT
EmployeeName,
Salary,
ROW_NUMBER()
OVER
(
ORDER BY Salary DESC
) AS RN
FROM Employees
)
SELECT *
FROM SalaryRanking
WHERE RN <= 3;Top N Records Using RANK()
WITH SalaryRanking AS
(
SELECT
EmployeeName,
Salary,
RANK()
OVER
(
ORDER BY Salary DESC
) AS Rnk
FROM Employees
)
SELECT *
FROM SalaryRanking
WHERE Rnk <= 3;Real-World Example: Student Ranking
Students:
| Student | Marks |
|---|---|
| Rahul | 95 |
| Priya | 90 |
| Amit | 90 |
Using:
RANK()Students with equal marks get the same rank.
Real-World Example: Sales Leaderboard
Salespersons ranked by:
Monthly Revenueusing:
DENSE_RANK()Real-World Example: Banking
Rank customers by:
Account BalanceReal-World Example: E-Commerce
Rank products by:
Sales QuantityReal-World Example: University System
Rank students by:
CGPA
Marks
AttendanceROW_NUMBER() vs RANK() vs DENSE_RANK()
| Feature | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| Unique Numbers | Yes | No | No |
| Duplicate Values Same Rank | No | Yes | Yes |
| Rank Gaps | No | Yes | No |
| Sequential | Yes | No | Yes |
| Best For Pagination | Yes | No | No |
| Best For Rankings | No | Yes | Yes |
When Should You Use ROW_NUMBER()?
Use when:
Unique Row Numbers Needed
Pagination Required
Top N Rows NeededWhen Should You Use RANK()?
Use when:
Competition Ranking Needed
Rank Gaps AcceptableExample:
Sports RankingsWhen Should You Use DENSE_RANK()?
Use when:
Ranking Without Gaps NeededExample:
Employee Performance RankingAdvantages
Easy Ranking
Simple implementation.
Supports Analytics
Widely used in reporting.
Handles Duplicates
Flexible ranking options.
Works with PARTITION BY
Department-wise rankings possible.
Useful for Top N Queries
Very common interview topic.
Common Mistakes
Forgetting ORDER BY
Ranking becomes meaningless.
Using ROW_NUMBER Instead of RANK
Duplicate values receive different numbers.
Confusing RANK and DENSE_RANK
Gap behavior differs.
Missing PARTITION BY
Can produce incorrect group rankings.
Best Practices
Always Define ORDER BY
Required for meaningful ranking.
Choose Correct Ranking Function
Understand gap behavior.
Use PARTITION BY for Group Rankings
Department-wise analysis.
Test Duplicate Values
Verify ranking logic.
Optimize Sorting Columns
Improve performance.
Common Interview Questions
What is the difference between RANK() and DENSE_RANK()?
RANK skips numbers after duplicates.
DENSE_RANK does not.
What is the difference between ROW_NUMBER() and RANK()?
ROW_NUMBER always assigns unique numbers.
RANK assigns the same rank to duplicates.
Which ranking function has no gaps?
DENSE_RANK()Which ranking function is best for pagination?
ROW_NUMBER()Are ranking functions Window Functions?
Yes.
They require:
OVER()Summary
ROW_NUMBER(), RANK(), and DENSE_RANK() are SQL Window Functions used for ranking rows. They differ mainly in how they handle duplicate values and rank gaps. These functions are widely used in reporting, analytics, leaderboards, and interview questions.
In this lesson, you learned:
- ROW_NUMBER()
- RANK()
- DENSE_RANK()
- Ranking differences
- PARTITION BY with ranking
- Top N queries
- Real-world examples
- Best practices
Mastering ranking functions is essential because they are among the most frequently used advanced SQL features in analytics, reporting, and technical interviews.
Next Step
Continue to the next lesson:
PIVOT and UNPIVOT →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for RANK, DENSERANK, and ROWNUMBER.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, advanced, rank
Related SQL Topics