Recursive Counter - SQL Programming

WITH RECURSIVE lets a query refer to its own previous rows. A stop condition keeps the recursion finite.

Program

Play the script to choose the upper bound and watch the recursive query produce a sequence.

recursive_counter.sql

WITH RECURSIVE params(max_n) AS (VALUES ()), numbers(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM numbers WHERE n < (SELECT max_n FROM params)) SELECT n FROM numbers ORDER BY n;

WITH RECURSIVE params(max_n) AS (VALUES ()), numbers(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM numbers WHERE n < (SELECT max_n FROM params)) SELECT n FROM numbers ORDER BY n;

WITH RECURSIVE params(max_n) AS (VALUES ()), numbers(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM numbers WHERE n < (SELECT max_n FROM params)) SELECT n FROM numbers ORDER BY n;

WITH RECURSIVE `WITH RECURSIVE` allows the `numbers` CTE to read rows it produced earlier.

anchor row `SELECT 1` creates the first row before recursion begins.

stop condition `n < max_n` prevents the recursive member from running forever.