ROW_NUMBER
Assign a unique sequential number to each row within a partition — the building block for top-N-per-group and pagination.
Syntax
ROW_NUMBER() OVER (
[PARTITION BY partition_expression, ...]
ORDER BY sort_expression [ASC | DESC], ...
)| Parameter | Type | Notes |
|---|---|---|
| PARTITION BY | expression, … | Optional. Restarts numbering per group. Without it, the whole result set is one partition. |
| ORDER BY* | expression [ASC|DESC], … | Decides numbering order. Ties are broken arbitrarily unless this includes a unique column. |
What it does
ROW_NUMBER() returns a unique integer for each row in the result set, counting
up from 1 within each partition. Unlike RANK/DENSE_RANK, ties never share a
number — the engine still has to put one of them first.
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 |
Reach for ROW_NUMBER when you need exactly one row per key — deduplication,
"latest row per customer," pagination — not when ties should be reported as equal.
Example
Loading editor…
Ties are not stable without a tiebreaker
Loading editor…
Category 1 has two products tied at 400 sales. ORDER BY sales DESC alone
doesn't say which gets rn = 1 — that pick can change between runs or engine
versions. Add a deterministic secondary key (ORDER BY sales DESC, product_id)
if it matters which one you get.
Filtering by row number
ROW_NUMBER() can't go in WHERE — wrap the query in a subquery or CTE and
filter the outer layer. This is the core mechanic behind
top N per group and
deduplicate rows (WHERE rn <= n / WHERE rn = 1).
- RANK — ties share a rank, then skip ahead.
- DENSE_RANK — ties share a rank, no gap afterwards.
- Deduplicate rows — ROW_NUMBER with
rn = 1to keep one row per key.
Safety check before shipping
- confirm the desired ordering is deterministic
- confirm whether a partition is required
- decide whether the result should be stable across repeated runs