🧠 DENSE_RANK(): Rank Without Gaps

Xorthax has learned how to rank rows, even when there are ties. But something still feels off.

“Why did the ranking skip a number?”

That’s where DENSE_RANK() comes in.

🚀 DENSE_RANK() + OVER()

DENSE_RANK() assigns a rank to each row, just like RANK()—but without gaps.

DENSE_RANK() OVER (
  PARTITION BY column
  ORDER BY column
)

This works because:

• PARTITION BY defines the group
• ORDER BY defines how rows are ranked
• Rows with the same value get the same rank

📊 Example: Rank Orders by Amount (No Gaps)

🧾 Sample Data

SELECT CustomerID, Amount,
       DENSE_RANK() OVER (
         PARTITION BY CustomerID
         ORDER BY Amount DESC
       ) AS DenseRankValue
FROM Orders;

Result:

Rows with the same value share the same rank, and the sequence continues without skipping numbers.

🧠 What’s Happening Here?

ORDER BY Amount DESC ranks rows from highest to lowest.

DENSE_RANK() assigns the same rank to tied rows.

Unlike RANK(), the next rank does not skip numbers.

That’s why the ranks go: 1, 2, 2, 3.

⚠️ Important: DENSE_RANK() still groups tied rows together, just like RANK().

The only difference is how the next rank is assigned—no gaps in the sequence.

🧪 Practice What You Just Learned

SELECT orderid, productid, DENSE_RANK() OVER (PARTITION BY productid ORDER BY quantity) AS dense_rank FROM orderdetails WHERE orderid BETWEEN 500 AND 600;

➡️ Next Step

You’ve now seen three ways to assign positions to rows.

Next, you’ll learn how to combine these techniques with other queries.

Want to go deeper?