ROW_NUMBER gives each row a position
Starting table: cafes
| name | rating |
|---|---|
| Morning Mug | 4.8 |
| Sunny Cup | 5 |
| Corner Bean | 4.8 |
ROW_NUMBER assigns 1, 2, 3 and so on in the order written inside OVER. Every café keeps its own row. Here the highest rating gets position 1; name breaks any rating ties so the numbering is predictable. The ORDER BY inside OVER decides the numbers, not the final display order — the last ORDER BY puts the result on screen in that order.
A tiny example
In plain English: Show me every café’s name with a position number, highest rating numbered 1.
SELECT name, ROW_NUMBER() OVER (ORDER BY rating DESC, name) AS position
FROM cafes
ORDER BY position;Query result
| name | position |
|---|---|
| Sunny Cup | 1 |
| Corner Bean | 2 |
| Morning Mug | 3 |
Sunny Cup has rating 5. The other two tie at 4.8, so Corner Bean gets position 2 before Morning Mug.
Related SQL lessons
Ready for a break?
A fresh, bite-sized SQL puzzle waits every morning. Tap tiles, see real results. No terminal, no setup, no timers.
Try today's free SQL puzzle