I was looking for a more accurate way to locate a top-level item in any hierarchy without having to do a lot of extraneous looping. The solution I found was is efficient and easy!
For this example, we will determine the top-level manager for any employee, regardless of the level of their managerial hierarchy. For this test, I have created a table called employees:
We can use the following method to determine an employee’s top-level manager and their relationship level:
USE [Test] GO DECLARE @EmployeeId int = 13 — Christian Lewis ;WITH EmployeeHierarchy AS ( SELECT e1.EmployeeId , e1.EmployeeName , e1.ManagerId , [Level] = 0 FROM Employees e1 WHERE (EmployeeId = @EmployeeId) UNION ALL SELECT e2.EmployeeId , e2.EmployeeName , e2.ManagerId , [Level] + 1 FROM Employees e2 INNER JOIN EmployeeHierarchy ON e2.EmployeeId = EmployeeHierarchy.ManagerId ) SELECT * FROM EmployeeHierarchy e3 WHERE e3.[Level] = (SELECT MAX([Level]) FROM EmployeeHierarchy)
Which will result in the following output: