How do you add a depth column to a recursive CTE that walks a manager_id chain?

## What the depth column is for An adjacency list stores a hierarchy as one row per node with a self-referencing pointer: `employees(id, name, manager_id)`, `categories(id, name, parent_id)`. The table itself has no notion of "level" — a node is one link from its parent and that is all the schema knows. When you traverse the list with a recursive CTE, you almost always want to know *how far* each returned row sits from the row you started at: to indent an org chart, to answer "show me two levels down", to sort managers above their reports, or to detect that a tree has become unexpectedly deep. SQL gives you nothing automatic here. There is no hidden level pseudo-column in a `WITH RECURSIVE` query. You compute the depth yourself, as an ordinary expression, and carry it through the iterations. ## Where the number comes from A recursive CTE has two members joined by `UNION ALL`. The anchor member produces the starting rows; the recursive member joins the base table to *the CTE itself*, and re-runs until it produces nothing new. The depth column follows exactly that structure: ```sql WITH RECURSIVE org (id, name, manager_id, depth) AS ( SELECT e.id, e.name, e.manager_id, 0 FROM employees e WHERE e.id = 42 -- start node UNION ALL SELECT c.id, c.name, c.manager_id, o.depth + 1 FROM employees c JOIN org o ON c.manager_id = o.id -- o = the CTE, c = the table ) SELECT id, name, depth FROM org ORDER BY depth, name; ``` The key detail is which side `depth + 1` is read from. `o` is the reference to the CTE — the rows produced by the previous iteration — so `o.depth + 1` is "one deeper than the row that led me here". `c` is the base table and has no depth column at all. Getting these backwards is the usual beginner error and produces either a compile error or, if you alias sloppily, a nonsense column. Whether to start at `0` or `1` is a convention. `0` reads as "number of links from the anchor row", which makes the anchor row's own depth zero and matches how most people describe distance. `1` reads as "level 1 of the org chart". Either is fine; pick one and keep it consistent, because every filter you write afterwards (`depth <= 3`) shifts by one between the two. ## Capping the walk A depth ceiling belongs **inside the recursive member**: ```sql JOIN org o ON c.manager_id = o.id WHERE o.depth < 3 ``` That predicate is evaluated as the rows are generated, so the engine simply never produces level 4 and the recursion reaches its fixpoint immediately after level 3. Writing `WHERE depth <= 3` in the final `SELECT` instead is functionally different in cost: the CTE still expands the whole subtree — possibly the entire company — and the outer filter discards it. On a deep or wide tree that difference is enormous, and on a *cyclic* tree the outer-filter version never finishes at all. ## Depth is not cycle protection A depth cap bounds the work, which is why people reach for it when data may be dirty. But it is a blunt instrument: it silently truncates legitimately deep branches, and within the cap it still happily emits the same node repeatedly if the data loops. Real cycle handling means tracking the nodes already visited on the current path (or using the standard `CYCLE` clause where the engine implements it) and refusing to expand one twice. Use the depth cap for "I only want three levels", not for "my data might loop". ## Using depth in the output Depth drives presentation. Indentation is `depth` copies of a pad string concatenated in front of the name — engines spell the repeat function differently (`REPEAT` in PostgreSQL and MySQL, `REPLICATE` in SQL Server). Note that `ORDER BY depth` gives you a breadth-first listing: all level-1 rows, then all level-2 rows, with siblings from different parents interleaved. To print an indented tree where each node sits directly under its parent you need an accumulated path column to sort on; depth alone cannot express that ordering. Also remember that a recursive CTE, like any query, returns rows in no defined order unless you write `ORDER BY`. The engine happens to generate rows level by level, but relying on that without an explicit sort is a bug waiting for a plan change. ## Common mistakes Reading `depth` from the base table alias instead of the CTE alias; assuming the engine tracks recursion depth for you; putting the level ceiling in the outer query and calling it a termination condition; and mixing 0-based and 1-based conventions between the query and the application that consumes it.