Hierarchical Query Using a Recursive CTE: Levels, Order and Loops

A hierarchical query using a recursive CTE walks a table that points at itself, one level at a time. An org chart is the classic case. Each row stores the ID of its boss, and the CTE follows those IDs from the top down. This post builds the levels, sorts the tree, walks upward and stops a loop.

The Sample Table

Ten people in a small garden shop. Maya has no boss, so the BossID of that row is NULL. Everyone else points at one person above them. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE Staff (StaffID INT PRIMARY KEY, StaffName VARCHAR(20) NOT NULL, BossID INT NULL);
GO

INSERT INTO Staff (StaffID, StaffName, BossID) VALUES
(1, 'Maya', NULL), (2, 'Arjun', 1), (3, 'Lena', 1), (4, 'Omar', 2), (5, 'Priya', 2),
(6, 'Noah', 3), (7, 'Iris', 4), (8, 'Sam', 4), (9, 'Tara', 6), (10, 'Ben', 7);
GO

The Four Parts

A CTE is a named result that lives for one statement. A recursive CTE refers to itself, so it can repeat. It needs four parts. An anchor query runs once. A recursive member runs again and again. A UNION ALL joins the two, and the recursive member joins back to the CTE.

Here the anchor is the person with no boss. The recursive member finds the people whose boss is already in the result.

Without the Join, Nothing Repeats

Start with the mistake. This version has an anchor and a second member that never mentions Org, with a fixed 2 for the level.

WITH Org AS (
  SELECT StaffID, StaffName, BossID, 1 AS Lvl FROM Staff WHERE BossID IS NULL
  UNION ALL
  SELECT StaffID, StaffName, BossID, 2 FROM Staff WHERE BossID IS NOT NULL
)
SELECT StaffID, StaffName, Lvl FROM Org;

SQL Server accepts it, but nothing is recursive. The second member runs once, and all 9 other people get level 2, even Ben, who is five levels down.

The Join and Lvl + 1

Join Staff back to Org on the boss column, and add 1 to the level of the boss already found. Each round then lands one level lower.

WITH Org AS (
  SELECT StaffID, StaffName, BossID, 1 AS Lvl FROM Staff WHERE BossID IS NULL
  UNION ALL
  SELECT s.StaffID, s.StaffName, s.BossID, o.Lvl + 1
  FROM Staff AS s INNER JOIN Org AS o ON s.BossID = o.StaffID
)
SELECT StaffID, StaffName, BossID, Lvl FROM Org ORDER BY StaffID;

Now Maya is level 1, and Ben is level 5. The rounds found 1, 2, 3, 3 and 1 people, one level at a time. One more round found nobody, and the recursion stopped.

Sorting the Tree

Sorting by level puts all of level 3 together, not each person under their boss. To fix that, build a path of names in the CTE and sort by it. REPLICATE then indents each name by its level.

WITH Org AS (
  SELECT StaffID, StaffName, 1 AS Lvl, CAST(StaffName AS VARCHAR(200)) AS Path FROM Staff WHERE BossID IS NULL
  UNION ALL
  SELECT s.StaffID, s.StaffName, o.Lvl + 1, CAST(o.Path + '/' + s.StaffName AS VARCHAR(200))
  FROM Staff AS s INNER JOIN Org AS o ON s.BossID = o.StaffID
)
SELECT REPLICATE('    ', Lvl - 1) + StaffName AS Tree, Lvl
FROM Org ORDER BY Path;

Ben appears under Iris, who appears under Omar, under Arjun, under Maya. Lena’s branch follows Arjun’s.

Walking Up Instead

Reverse the join to climb. Start at one person, find their boss, and repeat until there is none.

WITH Chain AS (
  SELECT StaffID, StaffName, BossID, 0 AS Steps FROM Staff WHERE StaffName = 'Ben'
  UNION ALL
  SELECT s.StaffID, s.StaffName, s.BossID, c.Steps + 1
  FROM Staff AS s INNER JOIN Chain AS c ON s.StaffID = c.BossID
)
SELECT Steps, StaffName FROM Chain ORDER BY Steps;

Ben is step 0, then Iris, Omar, Arjun and Maya, in steps 1 to 4. The same pattern lists every manager above one person.

A Loop in the Data

The recursion stops after 100 rounds unless you change it. A loop in the data hits that limit. Make Arjun report to Ben, who works below Arjun, and run the top-down query again.

UPDATE Staff SET BossID = 10 WHERE StaffID = 2;

WITH Org AS (
  SELECT StaffID, StaffName, BossID, 1 AS Lvl FROM Staff WHERE BossID IS NULL
  UNION ALL
  SELECT s.StaffID, s.StaffName, s.BossID, o.Lvl + 1
  FROM Staff AS s INNER JOIN Org AS o ON s.BossID = o.StaffID
)
SELECT StaffID, StaffName, BossID, Lvl FROM Org ORDER BY StaffID;

No error, but only 4 of the 10 people come back. The loop has no top, so the walk from Maya can’t reach it. A walk upward from Ben meets the loop.

WITH Chain AS (
  SELECT StaffID, StaffName, BossID, 0 AS Steps FROM Staff WHERE StaffName = 'Ben'
  UNION ALL
  SELECT s.StaffID, s.StaffName, s.BossID, c.Steps + 1
  FROM Staff AS s INNER JOIN Chain AS c ON s.StaffID = c.BossID
)
SELECT Steps, StaffName FROM Chain ORDER BY Steps;

The error is message 530: the maximum recursion 100 has been exhausted before statement completion. A lower limit stops sooner, with the same message.

WITH Chain AS (
  SELECT StaffID, StaffName, BossID, 0 AS Steps FROM Staff WHERE StaffName = 'Ben'
  UNION ALL
  SELECT s.StaffID, s.StaffName, s.BossID, c.Steps + 1
  FROM Staff AS s INNER JOIN Chain AS c ON s.StaffID = c.BossID
)
SELECT Steps, StaffName FROM Chain ORDER BY Steps
OPTION (MAXRECURSION 20);

The limit only protects you. Fix the data, and the same query returns five rows again.

UPDATE Staff SET BossID = 1 WHERE StaffID = 2;

Clean Up

Remove the sample table when you finish.

DROP TABLE Staff;

What to Remember

A recursive CTE has an anchor, a recursive member, a UNION ALL and a join back to itself. Add 1 to the level in the recursive member. Sort by a path to keep each person under their boss. A loop in the data hits the recursion limit, so fix the data.

To go further, read Recursive CTE Cycles: Detect Loops and Depth Boundaries, Recursive CTE Performance: Index the Child Lookup and Building an Org Chart Query With hierarchyid.

A recursive CTE is not a loop you write, it is a question you keep asking until nobody answers.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.