Q:
Sorting hierachy data alphabetically in SQL Server 2005
I have a follow-up question to the article Navigate hierarchies using recursive common table expressions. I have an employee hierarchy that follows a similar pattern to the article, but I need to be able to sort my data alphabetically within each level. Do you have any advice on how to accomplish this?
ASKED:
Jul 1 2008 8:02 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
WITH EmployeeCTE
AS
(
SELECT
EmployeeID,
1 AS Level
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT
E.EmployeeID,
x.Level + 1 AS Level
FROM HumanResources.Employee E
JOIN EmployeeCTE x ON x.EmployeeID = E.ManagerID
)
SELECT
EmployeeID,
Level
FROM EmployeeCTE
You can do an order by on the Level, then the LastName.
