sql · level 3 · unit 4 · lesson 1
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
A builds new rows from its own last rows.
Some answers need repeating: count from 1 to 5, list every date in a month, follow a chain of bosses. A recursive CTE does it. Start with , then write two queries joined by UNION ALL.
The first query is the . It makes the starting rows, here the number 1. The second query reads the rows made last round and makes the next ones, here n + 1.
The WHERE in the second query is the stop condition. When it lets no rows through, the repeating ends. Without it, the query never stops.
WITH RECURSIVE counter(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter ORDER BY n;counter(n) names the CTE's column, a shortcut that works in any CTE.
guess first
Doubling until 20. Where does it stop?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
WITH RECURSIVE doubles(n) AS (
SELECT 1
UNION ALL
SELECT n * 2 FROM doubles WHERE n < 20
)
SELECT n FROM doubles ORDER BY n;your turn
Your turn. Build a countdown.
Pick a starting number, such as 10, for the anchor. Then fill the stop condition so the countdown ends at exactly 1.
result
Your answer appears here as a table.
fix it
A week with eight days? Fix it!
result
Your answer appears here as a table.
- Count the rows. Which extra number crept in?
- The stop test checks the last row made. When n is 7,
n <= 7still passes, so 8 gets made. - Change n <= 7 to n < 7 on line 4, then press run.
nailed it
you made sql repeat itself. :D
WITH RECURSIVElets a CTE read its own rows.- The anchor starts it, the step repeats, the
WHEREstops it. - Check the stop condition for off-by-one mistakes.
new words in your collection
- recursive CTE
- WITH RECURSIVE
- anchor
This lesson isn’t saved. A free account remembers every lesson you finish.