1. Learn
    2. /
  2. Courses
    3. /
  3. Hierarchical and Recursive Queries in SQL Server

Exercise

Get the hierarchy position

An important problem when dealing with recursion is tracking the level of recursion. In the IT organization, this means keeping track of the position in the hierarchy of each employee.

A section of the entire hierarchy

For this, you will use a LEVEL field which keeps track of the current recursion step. You have to introduce the field in the anchor member, and increment this value on each recursion step in the recursion member.

Keep in mind, the first hierarchy level is 0, the second level is 1 and so on.

Instructions

100 XP
  • Initialize the field LEVEL to 1 at the start of the recursion.
  • Select the information of the IT director as the initial step of the CTE by filtering on Supervisor.
  • Set LEVEL to the current recursion step.
  • Perform a JOIN with the defined CTE on the IDs of the supervisor and the employee.