Recursive CTE in SQL

Level 5134 keystrokes

Recursive CTE calls itself to generate sequences or traverse hierarchies.

SQL code

-- WITH RECURSIVE lets the CTE nums refer to itself
1WITH RECURSIVE nums AS (
-- Anchor part: the starting row, n = 1
2 SELECT 1 AS n
-- UNION ALL adds the rows produced by the recursive part
3 UNION ALL
-- Recursive part: build n + 1 from the row created in the previous step
4 SELECT n + 1
5 FROM nums
-- Stop condition: once n reaches 10, no new row is made
6 WHERE n < 10
7)
-- Use the 10 numbers: each n with its square
8SELECT
9 n,
10 n * n AS square
11FROM
12 nums
-- Sort from 1 to 10
13ORDER BY
14 n;

Output

+----+--------+
| n  | square |
+----+--------+
|  1 |      1 |
|  2 |      4 |
|  3 |      9 |
|  4 |     16 |
|  5 |     25 |
|  6 |     36 |
|  7 |     49 |
|  8 |     64 |
|  9 |     81 |
| 10 |    100 |
+----+--------+
10 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • ,× 1
  • ;× 1
  • (× 1
  • )× 1
  • *× 1
  • +× 1
  • <× 1

More SQL examples at level 5

← SQL typing practice