I have create this SQL Fiddle with the following data
userId userName managerId
====== ======== =========
1 Adam NULL
2 Brett 1
3 Chris 2
4 George 1
5 David 3
6 Elliot 5
7 Fred 5
8 Harry 4
How do I return a tree such that the data is returned in the following order:
Adam
Brett
Chris
David
Elliot
Fred
George
Harry
I'm not worried about indenting and of course I can't just order by name (just in case Fred is corrected to Alfred).
This is what I've got so far:
WITH UserCTE AS (
SELECT userId, userName, managerId, 0 AS EmpLevel
FROM Users where managerId is null
UNION ALL
SELECT usr.userId, usr.userName, usr.managerId, mgr.[EmpLevel]+1
FROM Users AS usr
INNER JOIN UserCTE AS mgr
ON usr.managerId = mgr.userId where usr.managerId IS NOT NULL
)
SELECT *
FROM UserCTE AS u
ORDER BY EmpLevel;