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

Privacy Settings
Name Enabled
Cookies
We use Cookies to give you a better website experience.
x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. You can also manage your preferences there.