🧠 RANK(): Assign Positions with Ties

Xorthax can now assign a unique number to each row. But sometimes rows should share the same position.

“What if two rows are tied?”

That’s what RANK() handles.

🚀 RANK() + OVER()

RANK() assigns a position to each row—but allows ties.

RANK() OVER (
  PARTITION BY column
  ORDER BY column
)

This works by doing the following:

• 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

🧾 Sample Data

SELECT CustomerID, Amount,
       RANK() OVER (
         PARTITION BY CustomerID
         ORDER BY Amount DESC
       ) AS RankValue
FROM Orders;

Result:

Rows with the same value share the same rank.

🧠 What’s Happening Here?

ORDER BY Amount DESC ranks rows from highest to lowest.

RANK() assigns the same number to tied rows.

After a tie, the next rank skips ahead.

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

⚠️ Important: If there are ties, RANK() will leave gaps in the sequence.

If you need rankings without gaps, you’ll use a different function next.

🧪 Practice What You Just Learned

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

➡️ Next Step

RANK() handles ties, but it leaves gaps in the ranking.

Next, you’ll learn how to rank rows without gaps.

Want to go deeper?