MS SQL Server DENSE_RANK() Function with Examples

The DENSE_RANK() function is a ranking function in SQL Server that assigns a rank to each row within a partition of a result set, like RANK(), but without gaps in ranking when duplicate values are found.

✅ Syntax

DENSE_RANK() OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression [ASC|DESC]
)
  

• PARTITION BY: (Optional) Divides the result set into partitions.
• ORDER BY: Defines the order of the rows for ranking.

📘 Key Characteristics

• Rows with equal values receive the same rank.
• No gaps are left in the ranking sequence.

Example: Ranks: 1, 2, 2, 3, 4 (compared to RANK() which would give: 1, 2, 2, 4, 5)

🧪 Example Table: Sales

📊 Example 1: DENSE_RANK() by Sales (No Partitioning)

SELECT
    EmployeeName,
    SalesAmount,
    DENSE_RANK() OVER (ORDER BY SalesAmount DESC) AS SalesRank
FROM Sales;
  

🔽 Output

Note: Bob and Carol share rank 3, but unlike RANK(), the next rank is 4, not 5.

📊 Example 2: DENSE_RANK() with PARTITION BY Region

SELECT
    EmployeeName,
    Region,
    SalesAmount,
    DENSE_RANK() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS RegionalRank
FROM Sales;
  

🔽 Output

Note: Ranks restart within each region using PARTITION BY.

🔄 Comparison with RANK() and ROW_NUMBER()

🧠 When to Use DENSE_RANK()

• When you want to rank rows without gaps.
• Useful in statistical analysis or reporting where equal values need the same rank but continuity matters.
• Ideal for scoring systems, percentile groups, and ranking within categories.

✅ Summary

• DENSE_RANK() is useful for ordered results with ties and no skipped ranks.
• PARTITION BY resets ranks by group.
• Great for leaderboards, sales competitions, and business reporting.