A Number Sequence
Part of the Beyond the Basics section of Coddy's SQL journey — lesson 20 of 27.
You met the WITH keyword in Fundamentals for naming a subquery, also known as a CTE (Common Table Expression). A recursive CTE goes one step further: it lets the subquery refer to itself, building up a result row-by-row.
The simplest version generates a sequence of numbers without an underlying table. The shape is always the same:
WITH RECURSIVE counter(n) AS (
SELECT 1 -- anchor: the starting row
UNION ALL
SELECT n + 1 FROM counter WHERE n < 5 -- recursive: build the next from the last
)
SELECT n FROM counterRead it as: start with n = 1, then keep adding rows where each n is the previous n + 1, stopping when the WHERE stops matching. The result is 1, 2, 3, 4, 5.
The two parts of the body are joined by UNION ALL. The first is the anchor: the seed rows. The second is the recursive step: it queries the CTE itself.
Challenge
EasyWrite a recursive CTE called powers with two columns:
i: the iteration index, starting at1p:2raised to thei-th power (2,4,8, …)
Stop once p would exceed 1000. Return both columns, ordered by i ascending.
Cheat sheet
A recursive CTE lets a subquery refer to itself, building results row-by-row:
WITH RECURSIVE counter(n) AS (
SELECT 1 -- anchor: starting row
UNION ALL
SELECT n + 1 FROM counter WHERE n < 5 -- recursive step
)
SELECT n FROM counter- Anchor: the seed row(s) (first
SELECT) - Recursive step: queries the CTE itself to produce the next row
- Stops when the
WHEREcondition no longer matches - The two parts are joined with
UNION ALL
Try it yourself
WITH RECURSIVE powers(i, p) AS (
-- anchor: i=1, p=2
-- recursive: i+1, p*2, until p reaches 1000
)
SELECT i, p FROM powers ORDER BY i
Test yourselfThis lesson includes a short quiz. Start the lesson to answer it and track your progress.