Get the number of generations?
In this exercise, we are going to look at a random family tree. The dataset family
consists of three columns, the ID
, the name
, and the ParentID
.
Your task is to calculate the number of generations. You will do this by counting all generations starting from the person with ParentID = 101
.
For this task, you have to calculate the LEVEL
of the recursion which represents the current level in the generation hierarchy. After that, you need to count the number of LEVEL
s by using COUNT()
. Recall that the count of level is the number of generations, for example, a level count of 100 means you found 100 generations.
This exercise is part of the course
Hierarchical and Recursive Queries in SQL Server
Exercise instructions
- Initialize the recursion start by setting the
ParentID
to101
. - Set
LEVEL
to the current recursion step. - Join the anchor member to the CTE on the ID of the parent and the child.
COUNT()
the number of generations.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
WITH children AS (
SELECT
ID,
Name,
ParentID,
0 as LEVEL
FROM family
-- Set the targeted parent as recursion start
WHERE ___ = ___
UNION ALL
SELECT
child.ID,
child.NAME,
child.ParentID,
-- Increment LEVEL by 1 each step
___ + ___
FROM family child
INNER JOIN children
-- Join the anchor query with the CTE
ON child.___ = children.___)
SELECT
-- Count the number of generations
___(___) as Generations
FROM children
OPTION(MAXRECURSION 300);