Numbers rows 1, 2, 3… in a given order — across the whole result or within each group.
ROW_NUMBER() OVER (ORDER BY <sort key>)
ROW_NUMBER() OVER (PARTITION BY <group> ORDER BY <sort key>)
A window function: it doesn't reduce the number of rows but adds a number to each one. PARTITION BY restarts the numbering in each group — that's how you build “top N in each category”.
employees
| name | department | salary | bonus | phone |
|---|---|---|---|---|
| Emma | backend | 1200 | 300 | +44 7700 900123 |
| Liam | backend | 1000 | NULL | NULL |
| Olivia | frontend | 1350 | 150 | +44 7700 900456 |
| Noah | frontend | 950 | NULL | +44 7700 900789 |
| Sophia | design | 1200 | 200 | NULL |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| name | salary | place |
|---|---|---|
| Olivia | 1350 | 1 |
| Emma | 1200 | 2 |
| Sophia | 1200 | 3 |
| Liam | 1000 | 4 |
| Noah | 950 | 5 |
Emma and Sophia earn the same, but they get different numbers — chance decides who's second. More on this under “Watch out”.
| name | department | salary | place |
|---|---|---|---|
| Emma | backend | 1200 | 1 |
| Liam | backend | 1000 | 2 |
| Sophia | design | 1200 | 1 |
| Olivia | frontend | 1350 | 1 |
| Noah | frontend | 950 | 2 |
| name | department | salary |
|---|---|---|
| Emma | backend | 1200 |
| Sophia | design | 1200 |
| Olivia | frontend | 1350 |
You can't use the number in the WHERE of the same query, so we number the rows in a subquery first and filter outside it.
Equal values still get different numbers, and which of them comes first is down to chance. Add a unique field such as id to ORDER BY to make the order stable.
If equal values should share a place, use RANK or DENSE_RANK.