RANK()
Rank rows with tied values getting the same position — gaps after ties
Advanced 10 minRANKrankingwindow
RANK: Ties Get the Same Number
RANK() gives tied values the same rank, then skips numbers after the tie. If two items share rank 2, the next item gets rank 4 — there is no rank 3. Here's how it works:
Ties share a rank — When multiple rows have the same value in the ORDER BY column, they all receive the same rank number. Two items tied at rank 2 both get rank 2.
Gaps follow ties — After a tie, the next rank skips ahead. The skip size equals the number of tied items. If 3 items tie at rank 2, the next item gets rank 5 — ranks 3 and 4 are never assigned.
How it differs from ROW_NUMBER — ROW_NUMBER always assigns unique sequential numbers (1, 2, 3, 4) and breaks ties arbitrarily. RANK preserves ties: 1, 2, 2, 4. Use ROW_NUMBER when you need unique numbering; use RANK when ties should be visible.
When to use RANK — Choose RANK when ties are meaningful and the rank should reflect how many items are ahead. If 2 people tie for 2nd place, the next person is correctly ranked 4th — there is no 3rd place. This is competition-style ranking.
Knowledge Canvas
How RANK Works
Rank with gaps after ties
- Tied values get the same rank
- Next rank after a tie SKIPS: 1, 2, 2, 4
- Gap = number of tied rows before it
- PARTITION BY creates independent ranking groups
RANK vs ROW_NUMBER
Ties vs unique numbering
RANK: 1, 2, 2, 4
Ties share rank, gaps follow
ROW_NUMBER: 1, 2, 3, 4
Always unique, no ties
When to Use RANK
Gap behavior is sometimes what you want
- Competition scoring: 2 silver medals → no bronze awarded
- Leaderboards where position matters absolutely
- Filtering: "top 3 ranks" may return 4+ rows with ties
- Use ROW_NUMBER if you want exactly one row per rank
RANK Gotchas
Gaps cause surprises
- WHERE rank <= 3 might return 2 rows (if rank jumps from 2 to 4)
- Or it might return 4 rows (if rank 3 has ties)
- Gaps make RANK unsuitable for "give me exactly N rows"
- Use ROW_NUMBER for exact row counts
Syntax
Syntax Template
1-- Single ranking across all rows
2RANK() OVER (
3 ORDER BY score DESC
4) AS rnk
5
6-- Ranking that restarts per group
7RANK() OVER (
8 PARTITION BY category
9 ORDER BY price DESC
10) AS category_rank
RANK()Same rank for ties, gaps after: 1, 2, 2, 4vs ROW_NUMBERROW_NUMBER never ties: 1, 2, 3, 4 Sample Data
reviews
12 rows
| product_id | rating |
|---|---|
| P101 | 5 |
| P101 | 4 |
| P101 | 3 |
| P102 | 5 |
| P102 | 4 |
| P103 | 4 |
| P104 | 5 |
| P105 | 5 |
| P105 | 4 |
| P107 | 3 |
| P108 | 5 |
| P109 | 5 |
products
10 rows
| id | name | category | price | stock |
|---|---|---|---|---|
| P101 | Wireless Mouse | Electronics | 24.99 | 150 |
| P102 | Office Desk | Furniture | 189 | 25 |
| P103 | LED Desk Lamp | Furniture | 45.5 | 80 |
| P104 | USB-C Cable | Electronics | 12.99 | 500 |
| P105 | Bluetooth Speaker | Electronics | 79 | 60 |
| P106 | Office Chair | Furniture | 95 | 15 |
| P107 | Coffee Maker | Appliances | 89.99 | 40 |
| P108 | Notebook Set | Stationery | 8.5 | 200 |
| P109 | Wireless Headphones | Electronics | 159 | 30 |
| P110 | Standing Desk | Furniture | 425 | 8 |
Worked Example
Rank products by their average rating, showing ties.
SQL
1WITH product_ratings AS (
2 SELECT
3 product_id,
4 ROUND(AVG(rating), 1) AS avg_rating
5 FROM reviews
6 GROUP BY
7 product_id
8)
9SELECT
10 p.name,
11 pr.avg_rating,
12 RANK() OVER (ORDER BY pr.avg_rating DESC) AS rnk
13FROM product_ratings pr
14JOIN products p
15 ON p.id = pr.product_id;
Products with identical average ratings get the same rank. After a tie, the next rank skips — if three products tie at rank 1, the next product is rank 4.
Output
5 rows
| name | avg_rating | rnk |
|---|---|---|
| USB-C Cable | 5 | 1 |
| Notebook Set | 5 | 1 |
| Wireless Headphones | 5 | 1 |
| Office Desk | 4.5 | 4 |
| Bluetooth Speaker | 4.5 | 4 |
Key Concepts
1RANK: same rank for ties, then skip (1, 2, 2, 4)
2Gap size = number of tied items before the next rank
3Competition-style: 2 people tie for 2nd → next is 4th, not 3rd
4RANK vs ROW_NUMBER: ties matter vs unique numbering needed
Pro Tip
RANK gives competition-style rankings where ties share a position and subsequent ranks reflect the actual count of items ahead.
When to Use
Leaderboards, competition rankings, exam results, sales performance rankings.
Rank all products by price (highest first). Show name, price, and rank. Use RANK().
Your Query