RANK(): Handling Ties with Gaps
What You'll Learn
You'll learn how RANK() assigns rankings that create gaps after tied values, making it ideal when you want ties to "count" toward the next position.
The Core Idea
While ROW_NUMBER() assigns strictly unique numbers, RANK() handles ties differently—it gives identical values the same rank, then skips positions afterward to account for how many rows tied.
Think of it like an Olympic medal ceremony: if two athletes tie for silver (both rank 2), there's no bronze medal awarded—the next athlete gets rank 4. The tie "uses up" two positions (2 and 3), creating a gap.
How RANK() Works
SELECT
employee_name,
sales_amount,
RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM sales_team;
If three employees all have $50,000 in sales (tied for 1st place), they all receive rank 1. The next employee automatically gets rank 4, not 2—because positions 1, 2, and 3 were "consumed" by the tie.
Example output:
employee_name | sales_amount | sales_rank
--------------|--------------|------------
Alice | 50000 | 1
Bob | 50000 | 1
Carol | 50000 | 1
Dave | 45000 | 4
Eve | 40000 | 5
Notice how ranks jump from 1 to 4, leaving gaps at 2 and 3.
When to Use RANK()
Use RANK() when:
- Ties should receive equal recognition
- You want the ranking to reflect that multiple items occupied those positions
- Gap-based ranking matches your business logic (like competition standings)
Key Takeaway: RANK() assigns the same rank to tied values and leaves gaps in the sequence equal to the number of ties, making it perfect for competitive rankings where ties "count" against subsequent positions.