skip to today's puzzle

ROW_NUMBER gives each row a position

Starting table: cafes

Starting cafes data before the example query
namerating
Morning Mug4.8
Sunny Cup5
Corner Bean4.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

nameposition
Sunny Cup1
Corner Bean2
Morning Mug3

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