Understanding ROW_NUMBER(), RANK(), and DENSE_RANK() in SQL
SQL provides powerful ranking functions to assign a rank or unique number to each row within a partition
of a result set.
The three commonly used functions are:
ROW_NUMBER()
RANK()
DENSE_RANK()
These functions are used with the OVER() clause, which defines how to order and partition the data.
1. ROW_NUMBER()
Assigns a unique sequential number to each row within a partition.
Starts at 1 for each partition.
No ties: Even if rows have the same value, they get different row numbers.
Syntax:
ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name)
2. RANK()
Assigns the same rank to tied rows.
Skips ranks for the next values (non-continuous).
Syntax:
RANK() OVER (PARTITION BY column_name ORDER BY column_name)
3. DENSE_RANK()
Similar to RANK(), but does not skip ranks after ties.
Syntax:
DENSE_RANK() OVER (PARTITION BY column_name ORDER BY column_name)
Comparison Table
Handles Skips
Function Use Case
Ties Ranks
ROW_NUMBER() No No Unique row numbering
RANK() Yes Yes Ranking with gaps
DENSE_RANK() Yes No Ranking without gaps
Like, Comment, and Repost!
Scenario 1: Fetch Top N Records per Group
Get the top 2 highest-paid employees from each department.
Input Table: Employee
EmpID Dept Salary
1 HR 60000
2 HR 55000
3 IT 80000
4 IT 75000
5 IT 80000
Query:
WITH RankedEmployees AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS RowNum
FROM Employee
)
SELECT * FROM RankedEmployees
WHERE RowNum <= 2;
Output:
EmpID Dept Salary RowNum
1 HR 60000 1
2 HR 55000 2
3 IT 80000 1
5 IT 80000 2
Scenario 2: Detect Duplicate Records
Input Table: Employee
EmpID Dept Salary
1 HR 60000
1 HR 60000
2 IT 75000
2 IT 75000
3 IT 80000
Query:
WITH Duplicates AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY EmpID, Dept, Salary ORDER BY EmpID) AS RowNum
FROM Employee
)
SELECT * FROM Duplicates
WHERE RowNum > 1;
Like, Comment, and Repost!
Output:
EmpID Dept Salary RowNum
1 HR 60000 2
2 IT 75000 2
Scenario 3: Ranking Products Based on Sales
Input Table: Sales
ProductID Region SalesAmount
101 North 2000
102 North 2000
103 North 1800
104 South 2500
105 South 2400
Query:
SELECT ProductID, Region, SalesAmount,
RANK() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS SalesRank
FROM Sales;
Output:
ProductID Region SalesAmount SalesRank
101 North 2000 1
102 North 2000 1
103 North 1800 3
104 South 2500 1
105 South 2400 2
Scenario 4: Rank Students Based on Marks
Input Table: Students
StudentID Marks
1 95
2 98
3 95
4 92
Query:
SELECT StudentID, Marks,
DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rank
FROM Students;
Like, Comment, and Repost!
Output:
StudentID Marks Rank
2 98 1
1 95 2
3 95 2
4 92 3
Scenario 5: Identify Latest Entry Per Group
Use ROW_NUMBER to fetch the latest transaction per customer.
Input Table: Transactions
TransID CustomerID Amount TransDate
1 C1 100 1/1/2024
2 C1 150 1/5/2024
3 C2 200 1/3/2024
4 C2 180 1/1/2024
Query:
WITH Ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY TransDate DESC) AS rn
FROM Transactions
)
SELECT * FROM Ranked WHERE rn = 1;
Output:
TransID CustomerID Amount TransDate rn
2 C1 150 1/5/2024 1
3 C2 200 1/3/2024 1
Like, Comment, and Repost!
