SQL Window Ranking Functions – ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), PERCENT_RANK(), CUME_DIST()
These are Window Functions (Analytic Functions) introduced in SQL Server to perform calculations across a set of rows without grouping them.
Unlike GROUP BY, window functions do not reduce the number of rows. Every row remains in the result.
Why do we need Ranking Functions?
Suppose you have an Employee table.
Employee
-------------------------------------
EmpId | Name | Department | Salary
-------------------------------------
1 | John | IT | 80000
2 | David | IT | 90000
3 | Smith | IT | 90000
4 | Alice | HR | 60000
5 | Bob | HR | 70000
6 | Mike | HR | 70000
7 | Tom | Sales | 50000
Suppose interviewer asks
Find highest salary employee.
Easy.
SELECT *
FROM Employee
ORDER BY Salary DESC;
But what if interviewer asks
Highest salary employee in each department
Top 3 salaries
Second highest salary
Remove duplicate records
Pagination
Rank employees
That's where ranking functions are used.
Syntax
FunctionName()
OVER
(
PARTITION BY column
ORDER BY column
)
There are two important clauses.
ORDER BY
Mandatory.
Determines ranking order.
Example
ORDER BY Salary DESC
PARTITION BY
Optional.
Splits data into groups before assigning ranks.
Example
PARTITION BY Department
Means every department ranking starts from 1.
1. ROW_NUMBER()
Assigns a unique sequential number to every row.
Even if salaries are equal,
numbers are different.
Example
SELECT
Name,
Salary,
ROW_NUMBER() OVER(ORDER BY Salary DESC) AS RowNum
FROM Employee;
Output
| Name | Salary | ROW_NUMBER |
|---|---|---|
| David | 90000 | 1 |
| Smith | 90000 | 2 |
| John | 80000 | 3 |
| Bob | 70000 | 4 |
| Mike | 70000 | 5 |
| Alice | 60000 | 6 |
| Tom | 50000 | 7 |
Notice
David and Smith have same salary.
Still
1
2
No ties.
Real-world use
Pagination
WITH CTE AS
(
SELECT *,
ROW_NUMBER() OVER(ORDER BY EmpId) RN
FROM Employee
)
SELECT *
FROM CTE
WHERE RN BETWEEN 11 AND 20;
This returns page 2.
Remove duplicates
Suppose duplicate emails exist.
WITH CTE AS
(
SELECT *,
ROW_NUMBER()
OVER(PARTITION BY Email ORDER BY EmpId) RN
FROM Customer
)
DELETE FROM CTE
WHERE RN>1;
Very common interview question.
2. RANK()
Assigns same rank to equal values.
But skips next rank.
Example
SELECT
Name,
Salary,
RANK() OVER(ORDER BY Salary DESC) RankNo
FROM Employee;
Output
| Name | Salary | Rank |
|---|---|---|
| David | 90000 | 1 |
| Smith | 90000 | 1 |
| John | 80000 | 3 |
| Bob | 70000 | 4 |
| Mike | 70000 | 4 |
| Alice | 60000 | 6 |
| Tom | 50000 | 7 |
Notice
1
1
3
Rank 2 is skipped.
Because two employees share rank 1.
Visualization
90000
David Rank 1
90000
Smith Rank 1
80000
John Rank 3
Rank 2 missing.
Use Case
Sports Competition
100 Marks
Student A
100 Marks
Student B
95 Marks
Student C
Ranks
1
1
3
Exactly like Olympics.
3. DENSE_RANK()
Similar to RANK()
But
No gaps.
Example
SELECT
Name,
Salary,
DENSE_RANK()
OVER(ORDER BY Salary DESC)
AS DenseRank
FROM Employee;
Output
| Name | Salary | Dense Rank |
|---|---|---|
| David | 90000 | 1 |
| Smith | 90000 | 1 |
| John | 80000 | 2 |
| Bob | 70000 | 3 |
| Mike | 70000 | 3 |
| Alice | 60000 | 4 |
| Tom | 50000 | 5 |
Notice
1
1
2
3
3
4
5
No skipped numbers.
Visualization
90000
David 1
90000
Smith 1
80000
John 2
70000
Bob 3
70000
Mike 3
Difference between ROW_NUMBER(), RANK(), DENSE_RANK()
| Salary | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 90000 | 1 | 1 | 1 |
| 90000 | 2 | 1 | 1 |
| 80000 | 3 | 3 | 2 |
| 70000 | 4 | 4 | 3 |
| 70000 | 5 | 4 | 3 |
| 60000 | 6 | 6 | 4 |
Visual Difference
ROW_NUMBER()
90000 1
90000 2
80000 3
70000 4
70000 5
RANK()
90000 1
90000 1
80000 3
70000 4
70000 4
60000 6
DENSE_RANK()
90000 1
90000 1
80000 2
70000 3
70000 3
60000 4
Which one should you use?
Need unique row number?
Use
ROW_NUMBER()
Examples
Pagination
Duplicate removal
Unique numbering
Need competition ranking?
Use
RANK()
Examples
Sports
Exam Ranking
Need consecutive ranking?
Use
DENSE_RANK()
Examples
Top N salaries
Product Ranking
Customer Ranking
PARTITION BY Example
Suppose
IT
John 80000
David 90000
Smith 90000
HR
Alice 60000
Bob 70000
Mike 70000
Query
SELECT
Department,
Name,
Salary,
DENSE_RANK()
OVER
(
PARTITION BY Department
ORDER BY Salary DESC
)
AS Rank
FROM Employee;
Output
| Dept | Name | Salary | Rank |
|---|---|---|---|
| IT | David | 90000 | 1 |
| IT | Smith | 90000 | 1 |
| IT | John | 80000 | 2 |
| HR | Bob | 70000 | 1 |
| HR | Mike | 70000 | 1 |
| HR | Alice | 60000 | 2 |
Notice ranking restarts for every department.
4. NTILE()
Splits rows into equal-sized groups (buckets).
Example
SELECT
Name,
Salary,
NTILE(4)
OVER(ORDER BY Salary DESC)
AS Quartile
FROM Employee;
If there are 20 rows,
Quartile 1
Top 25%
Quartile 2
Next 25%
Quartile 3
Next
Quartile 4
Lowest
Used in
Salary bands
Performance categories
Customer segmentation
5. PERCENT_RANK()
Returns the relative rank of a row as a value between 0 and 1.
Formula:
(RANK - 1) / (TotalRows - 1)
Example
SELECT
Name,
Salary,
PERCENT_RANK() OVER(ORDER BY Salary DESC) AS PercentRank
FROM Employee;
Sample output
| Name | Salary | PercentRank |
|---|---|---|
| David | 90000 | 0.00 |
| Smith | 90000 | 0.00 |
| John | 80000 | 0.33 |
| Bob | 70000 | 0.50 |
| Mike | 70000 | 0.50 |
| Alice | 60000 | 0.83 |
| Tom | 50000 | 1.00 |
Use cases:
Percentile analysis
Academic grading
Sales performance evaluation
6. CUME_DIST()
Returns the cumulative distribution, i.e., the proportion of rows with values less than or equal to the current row.
Example
SELECT
Name,
Salary,
CUME_DIST() OVER(ORDER BY Salary DESC) AS CumulativeDist
FROM Employee;
Sample output
| Name | Salary | CUME_DIST |
|---|---|---|
| David | 90000 | 0.29 |
| Smith | 90000 | 0.29 |
| John | 80000 | 0.43 |
| Bob | 70000 | 0.71 |
| Mike | 70000 | 0.71 |
| Alice | 60000 | 0.86 |
| Tom | 50000 | 1.00 |
Use cases:
Finding top performers
Statistical analysis
Distribution reporting
Common Interview Questions
1. Find the second highest salary.
Using DENSE_RANK():
WITH SalaryRank AS
(
SELECT *,
DENSE_RANK() OVER(ORDER BY Salary DESC) AS SalaryRank
FROM Employee
)
SELECT *
FROM SalaryRank
WHERE SalaryRank = 2;
2. Find the top 3 highest salaries.
WITH SalaryRank AS
(
SELECT *,
DENSE_RANK() OVER(ORDER BY Salary DESC) AS SalaryRank
FROM Employee
)
SELECT *
FROM SalaryRank
WHERE SalaryRank <= 3;
3. Find the highest-paid employee in each department.
WITH DeptRank AS
(
SELECT *,
DENSE_RANK() OVER(
PARTITION BY Department
ORDER BY Salary DESC
) AS DeptRank
FROM Employee
)
SELECT *
FROM DeptRank
WHERE DeptRank = 1;
4. Remove duplicate rows.
WITH DuplicateRows AS
(
SELECT *,
ROW_NUMBER() OVER(
PARTITION BY Email
ORDER BY EmpId
) AS RN
FROM Customer
)
DELETE FROM DuplicateRows
WHERE RN > 1;
5. Implement pagination (rows 21–30).
WITH OrderedEmployees AS
(
SELECT *,
ROW_NUMBER() OVER(ORDER BY EmpId) AS RN
FROM Employee
)
SELECT *
FROM OrderedEmployees
WHERE RN BETWEEN 21 AND 30;
Quick Summary
| Function | Duplicates Get Same Rank? | Gaps in Ranking? | Common Use Cases |
|---|---|---|---|
ROW_NUMBER() | ❌ No | ❌ No | Pagination, duplicate removal, unique sequencing |
RANK() | ✅ Yes | ✅ Yes | Competition ranking, exam results |
DENSE_RANK() | ✅ Yes | ❌ No | Top N salaries, leaderboards |
NTILE(n) | N/A | N/A | Divide rows into equal groups or quartiles |
PERCENT_RANK() | Based on RANK() | May reflect rank gaps | Percentile calculations |
CUME_DIST() | Includes ties together | N/A | Cumulative distribution and statistical reporting |
Interview Tip
A very common interview question is:
"What is the difference between
ROW_NUMBER(),RANK(), andDENSE_RANK()?"
A concise answer:
ROW_NUMBER()gives every row a unique sequential number, even when values are tied.RANK()assigns the same rank to tied rows but skips the next rank(s).DENSE_RANK()assigns the same rank to tied rows without leaving gaps.
Understanding these differences, along with PARTITION BY and ORDER BY, is essential for solving real-world SQL problems such as pagination, leaderboards, Top-N queries, duplicate removal, and departmental rankings.
