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 LEVELs 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.

Diese Übung ist Teil des Kurses

Hierarchical and Recursive Queries in SQL Server

Kurs anzeigen

Anleitung zur Übung

Initialize the recursion start by setting the ParentID to 101.
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.

Interaktive Übung

Versuche dich an dieser Übung, indem du diesen Beispielcode vervollständigst.

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);

Code bearbeiten und ausführen