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.
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.