Number Sequence Generator

basic

Generate numbers 1 to 10 without a numbers table

WITH RECURSIVE numbers AS (

-- Anchor: Start at 1

SELECT 1 AS n

UNION ALL

-- Recursive: Add 1 each iteration

SELECT n + 1

FROM numbers

WHERE n < 10 -- Termination: Stop at 10

)

SELECT * FROM numbers;

n

1

2

3

4

5

6

7

8

9

10

Anchor returns 1. Recursive adds 1 each iteration. Terminates when n reaches 10. Useful for generating test data, date ranges, or filling gaps.

All

Employee Org Chart - Top Down

intermediate

Show CEO and all employees reporting under them with hierarchy levels

WITH RECURSIVE org_chart AS (

-- Anchor: Start with CEO (no manager)

SELECT

id,

name,

manager_id,

title,

0 AS level,

CAST(name AS VARCHAR(1000)) AS path

FROM employees

WHERE manager_id IS NULL