Tracking depth

Compute each row's depth level.

Depth level

In a recursive CTE the level column shows the hierarchy depth. It increments each step.

SELECT name, level FROM org ORDER BY level;

This query shows each person's level.

name, level is selected from org. The result is sorted by level in ascending order.

Depth level — details

level starts at 1 in the anchor, +1 in the recursive step. Depth makes the hierarchy easier to understand.

If you sort the result with ORDER BY level you see the top manager first and then the people below, level by level; WHERE level <= 2 returns only the first two levels.

Exercise Depth level

Sort the recursive CTE results by the level column.

Show example solution

SELECT name, level FROM org ORDER BY level;

Test yourself

What happens to level each step?
- Stays the same
- Increases by 1
- Decreases
- Resets to zero
Show answer

Increases by 1 — Level increments by +1 each step.

Add ORDER BY

SELECT name, level FROM org ____ level;

Show answer

SELECT name, level FROM org ORDER BY level;

Tracking depth: match the terms

level

depth
+1

increments
anchor

1
step

loop

Next lesson: Stopping recursion

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies