Hierarchical Resultset Sorting

I have a query like this

with TempCTE(id, Name, level, sortcol)
As
(
Select id, Name, 0 as level,
cast(cast( id AS BINARY(4)) as varbinary(100)) sortcol
from Table1
where id = 1

union all

Select id, Name, 0 as level,
cast(sortcol + cast( id AS BINARY(4)) as varbinary(100)) sortcol

from Table1 inner join TempCTE on TempCTE.id = Table1.parentid
)

select * from TempCTE order by sortcol

My problem is I want to sort this hierarchical resultset further on name like

aaa
--aaaa
--bbbb
--cccc
aaaaa
bbbbb
--dddd
aaaaa
bbbbb
bbb
--aaaa
--bbbb

Thanks

You need to basically add the appropriate columns in the ORDER BY clause. Based on your query above, you need to probably do ORDER BY sortcol, name. See below example based on a query using AdventureWorks sample tables:

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT replicate('.', EmployeeLevel* 10) + right(replicate('0', 10) + cast(coalesce(ManagerID, 0) as varchar), 10) as Mgr
FROM DirectReports
ORDER BY EmployeeLevel, ManagerID, EmployeeID;
GO