;)
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:
Neat!
