sql - recursive employee hierarchy pinpointing a direct manager (with hierarchical order)

sql.sql

--taken directly from https://blogs.msdn.microsoft.com/simonince/2007/10/17/hierarchies-with-common-table-expressions/
--order logic taken from https://stackoverflow.com/questions/32858774/how-to-order-rows-by-hierarchy

/*
	Org chart
	----------
	matt (1)
		kirk (2)
			chris (3)
			tom (4)
		manny (5)
			tim (6)
				student1 (7)
				student2 (8)
			kush (9)
				student3 (10)
				student4 (11)
*/

;with emps as (
	select *
	from (
		values
			 (1, 'matt', null)	--top of the organization, no manager
			,(2, 'kirk', 1)
			,(3, 'chris', 2)
			,(4, 'tom', 2)
			,(5, 'manny', 1)
			,(6, 'tim', 5)
			,(7, 'student1', 6)
			,(8, 'student2', 6)
			,(9, 'kush', 5)
			,(10, 'student3', 9)
			,(11, 'student4', 9)
	) s (id, name, managerId)
)

,org as (
	select	level = 1
			,emps.id
			,emps.name
			,emps.managerId
			,root = emps.id

			--we begin the sort order when the lowest number, because this is the "top" of the organization
			,sortOrder = cast(1 as real)	

	from	emps

	where	1=1
			--we dont really need filters here, because we assign a root Id for every person in the employees table, then recursively work our way down.
			--notice that we do not alter the root Id in the second select because we dont want to modify it

			--and managerId is null
			--and id = 5

	union all

	select	level + 1
			,emps.id
			,emps.name
			,emps.managerId
			,root

			--we take the top sort order and calculate their subpositions
			,sortOrder = org.sortOrder + (CAST(ROW_NUMBER() OVER(ORDER BY emps.name) AS REAL) / POWER(10, org.level))

	from		emps
	inner join	org on org.id = emps.managerId
)

select 
		top 100
		*

from		org
where		root = 1	--put the manager ID here and get all the direct and indirect reports for this manager Id
order by	sortOrder


/*
level       id          name     managerId   root        sortOrder
----------- ----------- -------- ----------- ----------- -------------
1           1           matt     NULL        1           1
2           2           kirk     1           1           1.1
3           3           chris    2           1           1.11
3           4           tom      2           1           1.12
2           5           manny    1           1           1.2
3           9           kush     5           1           1.21
4           10          student3 9           1           1.211
4           11          student4 9           1           1.212
3           6           tim      5           1           1.22
4           7           student1 6           1           1.221
4           8           student2 6           1           1.222

*/