Skip to content
Advertisement

Find the top of the Hierarchy

I have the following table: employees

employee   emp_id manager  manager_id
a          1      x        1
b          2      a        1
c          3      b        2

I am trying to show x 1 is the top manager for a, b and c, even tho a 1 is b 2’s manager and b 2 is c 3’s manager. This is the what I am trying to accomplish:

employee employee_id top_manager top_manager_id
a        1           x           1 
b        2           x           1
c        3           x           1

The code I have tried

 WITH 
    report AS 
    (
     SELECT employee, employee_id, top_manager, top_manager_id
       FROM employees
      UNION ALL
     SELECT e.employee, e.employee_id, e.top_manager, e.top_manager_id
       FROM dbo.employees e
      INNER JOIN report r ON e.employee_id = r.top_manager_id 
    )
     SELECT employee, employee_id, top_manager, top_manager_id
       FROM report

Advertisement

Answer

Using recursive CTE:

;WITH all_managers_per_employee (employee_id, manager, manager_id)
AS
(
   select employee_id, manager, manager_id from employees -- Anchor member
   UNION ALL
   select all_managers_per_employee.employee_id, line_manager.manager, line_manager.manager_id -- Recursive member; references cte table, all_managers_per_employee .
   from all_managers_per_employee 
     join employees line_manager on line_manager.employee = all_managers_per_employee.manager
   where line_manager.manager <> line_manager.employee
),
tops (employee)
AS
(
    select distinct manager as employee
    from employees
    where manager not in (select employee from employees where manager<>employee)
)
select employees.employee, employees.employee_id, tops.employee as top_manager, all_managers_per_employee.manager_id as top_manager_id 
from all_managers_per_employee
    join tops on tops.employee = all_managers_per_employee.manager
    join employees on employees.employee_id = all_managers_per_employee.employee_id
order by 3,1;
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement