sql · level 3 · unit 2 · lesson 2
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
numbers rows in the order you choose.
ROW_NUMBER() OVER (ORDER BY total DESC) numbers the rows: 1 for the biggest order, 2 for the next, and so on. The ORDER BY inside OVER decides the numbers. The query's own ORDER BY, at the end, decides how rows are shown.
Watch for a : two rows with the same value. Orders 1 and 8 are both £30, so which is 3rd and which is 4th? SQL does not promise. The numbers could change between versions.
Add a , a second column that settles ties. ORDER BY total DESC, id numbers the same way every time.
SELECT id, total,
ROW_NUMBER() OVER (ORDER BY total DESC, id) AS position
FROM orders
ORDER BY position;An id makes a perfect tie-breaker, because no two rows share one.
guess first
Numbered by amount, shown by day. What appears?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT day, amount,
ROW_NUMBER() OVER (ORDER BY amount DESC, day) AS n
FROM takings
ORDER BY day
LIMIT 3;your turn
Your turn. Give every order a ticket number.
Number the orders from first to last by day. Several orders share a day, so use id as the tie-breaker: smaller ids first.
result
Your answer appears here as a table.
fix it
The top three orders are the smallest ones. Fix it!
result
Your answer appears here as a table.
- Number 1 went to the smallest order. Which way does the window sort?
- Inside
OVER,ORDER BY totalsorts small to big, like any otherORDER BY. - Add DESC after total inside OVER, so it reads ORDER BY total DESC, id, then press run.
nailed it
you numbered rows with no wobble. :D
ROW_NUMBER() OVER (ORDER BY ...)numbers rows 1, 2, 3.- The window's
ORDER BYand the query'sORDER BYare separate. - Add a tie-breaker so the numbers never change.
new words in your collection
- ROW_NUMBER
- tie
- tie-breaker
This lesson isn’t saved. A free account remembers every lesson you finish.