SQL 2005 - Generic Table Expression - Finding the Last in a Hierarchy

Question

Let's say I have the following table:

CREATE TABLE Employees ( EmployeeId int PRIMARY KEY NOT NULL, ParentEmployeId int REFERENCES Employees(EmployeeId) NULL, Name varChar(255) ) 

All records have a primary identifier, and records can identify another record as a parent. (My actual diagram does not apply to employees, this is just a simplified version to illustrate, so if you have a better way of processing information about employees, this is not related to this conversation.)

The following entries are inserted:

 INSERT INTO Employees VALUES (1, NULL, 'Company President 1') INSERT INTO Employees VALUES (2, NULL, 'Company President 2') INSERT INTO Employees VALUES (3, 1, 'Company President 1 - VP') INSERT INTO Employees VALUES (4, 2, 'Company President 2 - VP') INSERT INTO Employees VALUES (5, 3, 'Company President 1 - VP - Secretary') INSERT INTO Employees VALUES (6, 4, 'Company President 2 - VP - Secretary') INSERT INTO Employees VALUES (7, 5, 'Company President 1 - VP - Secretary - Sandwich Delivery') 

These inserts are:

 Company President 1 Company President 1 - VP Company President 1 - VP - Secretary Company President 1 - VP - Secretary - Sandwich Delivery Company President 2 Company President 2 - VP Company President 2 - VP - Secretary 

What I'm trying to do, for all employees who have NULL ParentEmployeeId , I want to find the last person in the chain, which in this example will be " Company President 1 - VP - Secretary - Sandwich Delivery " and " Company President 2 - VP - Secretary ".

I have the next CTE that gives me everything, including the nesting level, but I'm not sure where to go from here. I would like to avoid cursors if possible.

In addition, and this is very important , I have a logic in another place that ensures that an employee can have only 1 subordinate. So, although the scheme technically allows this, Company President 1 will never have the two VPs listed.

 WITH EmployeeRec(EmployeeId, ParentEmployeeId, Name, Level) AS ( SELECT EmployeeId, ParentEmployeId, Name, 1 as [Level] FROM Employees WHERE ParentEmployeId IS NULL UNION ALL SELECT E.EmployeeId, E.ParentEmployeId, E.Name, R.[Level] + 1 FROM Employees E INNER JOIN EmployeeRec R ON E.ParentEmployeId = R.EmployeeId ) SELECT * FROM EmployeeRec 

Answer 1

Tracking your EmployeeID wizard allows you to join the results with the latest level to save your records.

 WITH EmployeeRec(Master, EmployeeId, ParentEmployeeId, Name, Level) AS ( SELECT [Master] = EmployeeId, EmployeeId, ParentEmployeId, Name, 1 as [Level] FROM Employees WHERE ParentEmployeId IS NULL UNION ALL SELECT R.Master, E.EmployeeId, E.ParentEmployeId, E.Name, R.[Level] + 1 FROM Employees E INNER JOIN EmployeeRec R ON E.ParentEmployeId = R.EmployeeId ) SELECT * FROM EmployeeRec er INNER JOIN ( SELECT Master, Level = MAX(Level) FROM EmployeeRec GROUP BY Master ) m ON m.Master = er.Master AND m.Level = er.Level 

Answer 2

The key here is to track the top level parent in a recursive CTE :

 ;WITH EmployeeRec( EmployeeId, ParentEmployeeId, UltimateGrandbossEmployeeId, Name, Level) AS ( SELECT EmployeeId, ParentEmployeeId, EmployeeId UltimateGrandbossEmployeeId, Name, 1 as [Level] FROM Employees WHERE ParentEmployeeId IS NULL UNION ALL SELECT E.EmployeeId, E.ParentEmployeeId, R.UltimateGrandbossEmployeeId, E.Name, R.[Level] + 1 FROM Employees E INNER JOIN EmployeeRec R ON E.ParentEmployeeId = R.EmployeeId ) 

... form an intermediate CTE to capture the bottom-up level ...

 SELECT UltimateGrandbossEmployeeId, Name, ROW_NUMBER() OVER (PARTITION BY UltimateGrandbossEmployeeId ORDER BY Level Desc ) BottomUp FROM EmployeeRec ) 

... for each ultimate grandfather, choose your deepest child:

 SELECT UltimateGrandbossEmployeeId, DeepestChildName FROM Inter WHERE BottomUp = 1 

(combine all these code fragments to form one query with two CTEs and a SELECT )

Results:

 1 Company President 1 - VP - Secretary - Sandwich Delivery 2 Company President 2 - VP - Secretary 

You can return the JOIN back to Employee to get the names of the Ultiamte final debuts or to track the names in the CTE, which makes sense in your actual situation.