Lesson 32 / 37 · Composing queries

A recursive CTE runs in rounds

The anchor finds the one employee without a manager. Each round then finds the people who report to someone the previous round found, one level deeper, and the recursion stops when a round finds nobody. The round column says which round added each row.

The query

WITH RECURSIVE chain AS (
  SELECT id, name, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, c.level + 1
  FROM employees e
  JOIN chain c ON c.id = e.manager_id
)
SELECT name, level
FROM chain
ORDER BY level, name

Try WHERE c.level < 2 in the recursive part: round 2 finds nobody to add, and the chain stops at 4 rows.

Next: A window function looks at other rows without collapsing them · All lessons