ROW_NUMBER gives each row a position number
ROW_NUMBER labels the rows 1, 2, 3 and so on, and never repeats a number even when two rows tie. ORDER BY inside OVER decides which row is number 1, while PARTITION BY restarts the counting at 1 for each group. Filtering on that number later is a common way to keep the first or latest row in every group.
A tiny example
In plain English: Show me every student's name with a position number, highest score numbered 1.
SELECT name, ROW_NUMBER() OVER (ORDER BY score DESC) AS place
FROM students;What comes back
| name | place |
|---|---|
| Nia | 1 |
| Theo | 2 |
| Ravi | 3 |
Scores sorted high to low, then numbered 1, 2, 3 in that order.
Related SQL lessons
Ready for a break?
Take on today's cozy puzzle. A fresh, bite-sized query waits every morning — no timers, no pressure.
play today's sip