Recursive CTEs

Recursive Counter

Build Rows Until a Limit

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.

max_n
recursive_counter.sql
Replay: real traced execution (multi-file project)
WITH RECURSIVE params(max_n) AS (VALUES (5)), 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 (3)), 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 (7)), 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;

  1. result ← 5 rows

    1WITH RECURSIVE params(max_n) AS (VALUES (5)), 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;
    values this step5 rowsresult

  1. result ← 3 rows

    1WITH RECURSIVE params(max_n) AS (VALUES (3)), 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;
    values this step3 rowsresult

  1. result ← 7 rows

    1WITH RECURSIVE params(max_n) AS (VALUES (7)), 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;
    values this step7 rowsresult
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.