Here's one way. I apologize for not taking the time to make a more optimal solution but it's been a long day and I'm too pooped to pop.

Basically, you need to add an interim table to replace the original Employee table as the source of the recursive CTE to make the sort path. This new table has a SortedChild column to replace the EmployeeID in the sort path calculation and a Parent column to use in the join criteria along with SortedChild.

SELECT SortedChild = ROW_NUMBER() OVER (ORDER BY EmployeeName)

, Parent = NULL

, EmployeeID

, ManagerID

, EmployeeName

INTO #SortedEmployee

FROM dbo.Employee

;

GO

CREATE UNIQUE CLUSTERED INDEX IX_SortedEmployee_Composite01

ON #SortedEmployee (EmployeeID);

UPDATE child

SET child.Parent = parent.SortedChild

FROM #SortedEmployee child

JOIN #SortedEmployee parent

ON parent.EmployeeID = child.ManagerID

;

--===== Display what we end up with.

-- This is not a part of the solution

SELECT * FROM #SortedEmployee ORDER BY SortedChild

;