When working with SQL, you'll often need to rank employees, customers, products, or transactions based on their performance. This is where SQL ranking functions become useful.
The three most commonly used ranking functions are:
RANK()
DENSE_RANK()
ROW_NUMBER()
Although they look similar, they behave differently when duplicate values exist. Understanding these differences is important for both real-world projects and SQL interviews.
Sample Data
Let's use the following employee table.
Employee
Department
Salary
Amit
IT
80000
Rahul
IT
75000
Priya
IT
75000
Neha
IT
70000
1. ROW_NUMBER()
Definition
ROW_NUMBER() assigns a unique number to every row, even if two rows have the same value.
Syntax
SELECT
Employee,
Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Row_Num
FROM Employees;
Output
Employee
Salary
Row_Number
Amit
80000
1
Rahul
75000
2
Priya
75000
3
Neha
70000
4
Key Point
Even though Rahul and Priya have the same salary, they receive different row numbers.
When to Use
Use ROW_NUMBER() when every row must have a unique position.
Examples:
Invoice Numbers
Transaction IDs
Pagination
Removing Duplicate Records
Agar hume har row ko ek unique number dena ho, chahe values same hi kyu na ho, tab hum ROW_NUMBER() ka use karte hain. Maan lo do employees ki salary same hai, phir bhi ROW_NUMBER() un dono ko alag-alag numberdega. Is function ka use tab kiya jata hai jab hume har record ki unique position chahiye hoti hai, jaise pagination, duplicate records remove karna ya top records nikalna.
2. RANK()
Definition
RANK() gives the same rank to duplicate values, but skips the next rank.
Syntax
SELECT
Employee,
Salary,
RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank
FROM Employees;
Output
Employee
Salary
Rank
Amit
80000
1
Rahul
75000
2
Priya
75000
2
Neha
70000
4
Notice that Rank 3 is skipped.
Key Point
Duplicate values receive the same rank.
The next rank is skipped.
Agar hume kisi data ko highest se lowest ya lowest se highest order me arrange karke uski position nikalni ho, tab hum RANK() function ka use karte hain.Agar do records ki value same ho, to dono ko same rank milti hai aur uske baad wali rank automatically skip ho jati hai.
3. DENSE_RANK()
Definition
DENSE_RANK() also gives the same rank to duplicate values, but it never skips the next rank.
Syntax
SELECT
Employee,
Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS Dense_Rank
FROM Employees;
Agar hume kisi data ki ranking nikalni ho aur duplicate values ko same rank deni ho, lekin uske baad wali ranking ko skip na karna ho, tab hum DENSE_RANK() ka use karte hain.Maan lo kisi company me employees ko salary ke basis par rank deni hai. Agar do employees ki salary same hai, to dono ko same rank milegi. Lekin RANK() ki tarah next rank skip nahi hogi.Isliye jab hume continuous ranking chahiye hoti hai, tab DENSE_RANK() sabse best choice hoti hai.
Comparison Table
Function
Duplicate Values
Rank Skipped
Unique Numbers
ROW_NUMBER()
❌ No
❌ No
✅ Yes
RANK()
✅ Yes
✅ Yes
❌ No
DENSE_RANK()
✅ Yes
❌ No
❌ No
Real Business Scenarios
🏥 Hospital Analytics
Find the highest-paid doctors in each department.
Use RANK() if doctors with the same salary should share the same position.
Use DENSE_RANK() if continuous ranking is required.
Use ROW_NUMBER() if every doctor must have a unique sequence number.
🏦 Financial Fraud Analytics
Rank customers based on total fraudulent transaction amount.
Customers with equal fraud amounts may receive the same rank using RANK() or DENSE_RANK().
🍽️ Zomato Analytics
Rank restaurants by rating within each city.
If two restaurants have the same rating:
RANK() → skips the next position.
DENSE_RANK() → keeps ranking continuous.
ROW_NUMBER() → assigns unique numbers.
Common Mistake
Many beginners think these three functions produce the same output.
They only behave differently when duplicate values exist.
If all values are unique, all three functions return identical rankings.
Interview Question
Q1. What is the difference between RANK() and DENSE_RANK()?
Answer:
RANK() skips the next rank after duplicate values.
DENSE_RANK() does not skip any ranks.
Q2. Which ranking function always generates unique numbers?
Answer:
ROW_NUMBER()
Q3. Which function is commonly used for pagination?
Answer:
ROW_NUMBER()
Quick Revision
Need
Function
Unique numbering
ROW_NUMBER()
Same rank with skipped numbers
RANK()
Same rank without skipped numbers
DENSE_RANK()
Conclusion
Although ROW_NUMBER(), RANK(), and DENSE_RANK() are all ranking functions, each serves a different purpose.
Choose ROW_NUMBER() when every row needs a unique sequence.
Choose RANK() when tied values should share the same rank and gaps are acceptable.
Choose DENSE_RANK() when tied values should share the same rank without leaving gaps.
Understanding these differences will help you write better SQL queries and answer interview questions with confidence.