DENSE_RANK() in SQL

🏅 DENSE_RANK() is a SQL window function that assigns a rank to each row based on a specified ordering. Rows with the same ordering value receive the same rank, but unlike RANK(), no rank numbers are skipped after tied values.

📖 What is DENSE_RANK()?

DENSE_RANK() evaluates rows according to the ORDER BY clause inside the OVER() clause. When two or more rows have the same ordering value, they receive the same rank, and the next distinct value receives the next consecutive rank.

Information

DENSE_RANK() is commonly used for leaderboards, rankings, performance reports, and analytical queries where ties should share the same rank without leaving gaps.

đŸŽ¯ Why Use DENSE_RANK()?

DENSE_RANK() is useful when tied values should receive the same ranking while maintaining consecutive rank numbers.

  • 📌 Rank records without gaps.
  • 📌 Handle tied values fairly.
  • 📌 Build consecutive leaderboards.
  • 📌 Compare performance within groups.
  • 📌 Generate analytical reports.

📋 Sample Table

EmployeeIDEmployeeNameDepartmentSalary
101AliceSales70000
102BobSales65000
103CharlieIT80000
104DavidIT80000
105EmmaHR55000

📝 Basic Syntax

DENSE_RANK() Syntax

SELECT
    column1,
    column2,
    DENSE_RANK() OVER (
        ORDER BY column_name
    ) AS DenseRank
FROM table_name;

The ORDER BY clause determines the ranking order.

💡 Example: Rank Employees by Salary

Overall Salary Ranking

SELECT
    EmployeeName,
    Salary,
    DENSE_RANK() OVER (
        ORDER BY Salary DESC
    ) AS SalaryRank
FROM Employees;

📊 Example Output

EmployeeNameSalarySalaryRank
Charlie800001
David800001
Alice700002
Bob650003
Emma550004

Remember

Charlie and David both receive Rank 1. The next employee receives Rank 2, not Rank 3, because DENSE_RANK() does not leave gaps.

💡 Example: Rank Within Each Department

Department Ranking

SELECT
    EmployeeName,
    Department,
    Salary,
    DENSE_RANK() OVER (
        PARTITION BY Department
        ORDER BY Salary DESC
    ) AS DepartmentRank
FROM Employees;

PARTITION BY restarts the ranking independently for each department.

📊 Example Output

EmployeeDepartmentSalaryDepartmentRank
AliceSales700001
BobSales650002
CharlieIT800001
DavidIT800001
EmmaHR550001

💡 Example: Top Two Salary Ranks

Retrieve Top Salary Ranks

SELECT *
FROM (
    SELECT
        EmployeeName,
        Salary,
        DENSE_RANK() OVER (
            ORDER BY Salary DESC
        ) AS SalaryRank
    FROM Employees
) RankedEmployees
WHERE SalaryRank <= 2;

This query returns employees whose salaries fall within the top two distinct salary ranks.

⚖ī¸ DENSE_RANK() vs RANK() vs ROW_NUMBER()

FunctionTies Share RankGaps After Ties
ROW_NUMBER()❌ NoNot Applicable
RANK()✅ Yes✅ Yes
DENSE_RANK()✅ Yes❌ No

đŸ’ŧ Real-World Applications

  • 🏅 Competition leaderboards with tied positions.
  • 💰 Rank employees by salary.
  • 🛒 Rank products by sales.
  • 📈 Identify top-performing customers.
  • 🎓 Display student rankings with tied scores.

🗄ī¸ Database Compatibility

Database SystemDENSE_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

  • ❌ Confusing DENSE_RANK() with RANK().
  • ❌ Omitting the ORDER BY clause inside OVER().
  • ❌ Using DENSE_RANK() when every row requires a unique number.
  • ❌ Forgetting to use PARTITION BY when rankings should restart for each group.

Warning

If multiple rows have identical ordering values, DENSE_RANK() assigns the same rank to each of them. If every row must have a unique number regardless of ties, use ROW_NUMBER() instead.

⚠ī¸ Best Practices

Best Practice

Use DENSE_RANK() when tied values should share the same rank without creating gaps, always specify a meaningful ORDER BY, use PARTITION BY for group-based rankings, and choose the ranking function that matches your business rules.

🚀 Key Points to Remember

  • 📌 DENSE_RANK() is a SQL window function.
  • 📌 Equal values receive the same rank.
  • 📌 No gaps appear after tied values.
  • 📌 It requires the OVER() clause.
  • 📌 PARTITION BY restarts rankings within groups.
  • 📌 It is ideal for consecutive rankings and analytical reports.
>>"DENSE_RANK() keeps rankings fair and consecutive, even when multiple rows share the same position."

Summary

✅ DENSE_RANK() is a powerful SQL window function that assigns rankings while allowing tied rows to share the same position without leaving gaps. It is ideal for leaderboards, business reports, and analytical queries where consecutive rankings are preferred over skipped rank numbers.