SQL Server: Finding Top Level Item in Hierarchy

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:

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:

Query-Result

Neat!

 

One Response

  1. Kunal October 5, 2015

Leave a Reply