RANK() in SQL
🏆 RANK() is a SQL window function that assigns a ranking to each row within a result set based on a specified ordering. Unlike ROW_NUMBER(), rows with the same ordering value receive the same rank, and the next rank is skipped, creating gaps in the ranking sequence.
📖 What is RANK()?
RANK() evaluates rows according to the ORDER BY clause inside the OVER() clause. Rows with equal values receive identical ranks. After a tie, the following rank is increased to account for the tied rows.
Information
RANK() is commonly used for leaderboards, competition rankings, top-performing employees, and analytical reports where ties should share the same position.
🎯 Why Use RANK()?
RANK() is ideal when equal values should receive the same ranking.
- 📌 Rank records based on a value.
- 📌 Handle tied values fairly.
- 📌 Build leaderboards.
- 📌 Compare performance within groups.
- 📌 Create analytical reports.
📋 Sample Table
| EmployeeID | EmployeeName | Department | Salary |
|---|---|---|---|
| 101 | Alice | Sales | 70000 |
| 102 | Bob | Sales | 65000 |
| 103 | Charlie | IT | 80000 |
| 104 | David | IT | 80000 |
| 105 | Emma | HR | 55000 |
📝 Basic Syntax
RANK() Syntax
SELECT
column1,
column2,
RANK() OVER (
ORDER BY column_name
) AS RankValue
FROM table_name;The ORDER BY clause determines how the rows are ranked.
💡 Example: Rank Employees by Salary
Overall Salary Ranking
SELECT
EmployeeName,
Salary,
RANK() OVER (
ORDER BY Salary DESC
) AS SalaryRank
FROM Employees;📊 Example Output
| EmployeeName | Salary | SalaryRank |
|---|---|---|
| Charlie | 80000 | 1 |
| David | 80000 | 1 |
| Alice | 70000 | 3 |
| Bob | 65000 | 4 |
| Emma | 55000 | 5 |
Remember
Notice that both Charlie and David receive Rank 1. The next employee receives Rank 3, not Rank 2, because RANK() leaves gaps after ties.
💡 Example: Rank Within Each Department
Department Ranking
SELECT
EmployeeName,
Department,
Salary,
RANK() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS DepartmentRank
FROM Employees;PARTITION BY restarts the ranking separately for each department.
📊 Example Output
| Employee | Department | Salary | DepartmentRank |
|---|---|---|---|
| Alice | Sales | 70000 | 1 |
| Bob | Sales | 65000 | 2 |
| Charlie | IT | 80000 | 1 |
| David | IT | 80000 | 1 |
| Emma | HR | 55000 | 1 |
💡 Example: Top Two Ranked Employees
Top Ranked Employees
SELECT *
FROM (
SELECT
EmployeeName,
Salary,
RANK() OVER (
ORDER BY Salary DESC
) AS SalaryRank
FROM Employees
) RankedEmployees
WHERE SalaryRank <= 2;Since tied rows share the same rank, the number of rows returned may be greater than two.
⚖️ RANK() vs ROW_NUMBER() vs DENSE_RANK()
| Function | Ties Share Rank | Gaps After Ties |
|---|---|---|
| ROW_NUMBER() | ❌ No | Not Applicable |
| RANK() | ✅ Yes | ✅ Yes |
| DENSE_RANK() | ✅ Yes | ❌ No |
💼 Real-World Applications
- 🏅 Sports competition rankings.
- 💰 Rank employees by salary.
- 🛒 Identify top-selling products.
- 📈 Rank customers by total purchases.
- 🎓 Display student exam rankings.
🗄️ Database Compatibility
| Database System | RANK() Support |
|---|---|
| MySQL | ✅ Supported in MySQL 8.0 and later. |
| PostgreSQL | ✅ Fully supported. |
| SQL Server | ✅ Fully supported. |
| Oracle | ✅ Fully supported. |
| SQLite | ✅ Supported in SQLite 3.25.0 and later. |
⚠️ Common Mistakes
- ❌ Expecting consecutive rankings after tied values.
- ❌ Confusing RANK() with ROW_NUMBER().
- ❌ Omitting ORDER BY inside the OVER() clause.
- ❌ Forgetting to use PARTITION BY when rankings should restart for each group.
Warning
When multiple rows have identical ordering values, RANK()assigns the same rank to all tied rows and skips subsequent rank numbers. If you need consecutive rankings without gaps, use DENSE_RANK()instead.
⚠️ Best Practices
Best Practice
Use RANK() when ties should receive the same position, always specify a deterministic ORDER BY, use PARTITION BY for group-based rankings, and choose ROW_NUMBER() or DENSE_RANK() if their ranking behavior better matches your business requirements.
🚀 Key Points to Remember
- 📌 RANK() is a SQL window function.
- 📌 Equal values receive the same rank.
- 📌 Ranking gaps appear after tied values.
- 📌 It requires the OVER() clause.
- 📌 PARTITION BY restarts rankings within groups.
- 📌 It is ideal for leaderboards and competition-style rankings.
>>"RANK() rewards ties fairly—even if it leaves a gap for the next competitor."
Summary
✅ RANK() is a powerful SQL window function that assigns rankings while allowing tied rows to share the same position. Because it skips rank numbers after ties, it is especially useful for competition rankings, leaderboards, and analytical reports where equal values deserve equal ranks.