RANK
Assign ranks while keeping tied values in the same rank group — and skipping ahead by the tie count.
Syntax
RANK() OVER (
[PARTITION BY partition_expression, ...]
ORDER BY sort_expression [ASC | DESC], ...
)| Parameter | Type | Notes |
|---|---|---|
| PARTITION BY | expression, … | Optional. Restarts ranking per group. Without it, the whole result set is one partition. |
| ORDER BY* | expression [ASC|DESC], … | Defines rank order. Rows equal under this ordering tie and share a rank. |
What it does
RANK() gives the same rank to tied rows, then skips ahead by the number of
ties — the next distinct value's rank equals its row position, not the previous
rank + 1.
Example: values 10, 10, 20 become ranks 1, 1, 3.
Compare the three ranking functions
| salary | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 90,000 | 1 | 1 | 1 |
| 90,000 | 2 | 1 | 1 |
| 80,000 | 3 | 3 ← gap | 2 ← no gap |
| 70,000 | 4 | 4 | 3 |
RANK is the one to reach for when "how many rows beat this one" matters —
e.g. a leaderboard where two people tied for 1st correctly pushes the next
person to 3rd, not 2nd.
Example
Loading editor…
Ada and Grace both earn 145,000 in department 10 — both rank 1. Linus is next
at rank 3, not 2: two rows occupied rank 1, so rank 2 is skipped.
Filtering by rank
Like every window function, RANK() can't go in WHERE (it hasn't been
computed at that stage) — wrap it in a subquery or CTE and filter the outer
query. See DENSE_RANK: filtering by rank
for the full pattern; the fix is identical.
- DENSE_RANK — same ties, but no gap afterwards (
1, 1, 2). - ROW_NUMBER — always unique; ties broken arbitrarily unless you add a tiebreaker.
- Top N per group — using any of the three to cap rows per group.
Safety questions
- do ties need to be grouped or differentiated?
- is the ordering stable enough to support repeatable ranking?
- do you need dense ranking rather than standard ranking?