I wrote a previous post about using a SQL CTE to create a Hierarchical result set. Today I needed to do the same thing except I also needed it to sort items properly. The table has a sortOrder column, and that sortOrder applies to all the items under each parent Item. You can’t just use the sort order to sort your result set because there are many items with sortOrder=1, sortOrder=2, sortOrder=3, etc.
In order to accomplish this I added a nvarchar(max) column and create string sortOrder that built up at each level of recursion. This is simple and fast on the small resultSets I’m working with.
I hope this saves someone an hour of their life.
with CTE as
SELECT f.Id,f.ParentId,f.AccountId, f.UserId, f.Description,f.SortOrder, f.FolderTypeId,
CAST( f.Name as nvarchar(max)) as Name,
CAST( f.SortOrder as nvarchar(max)) as MasterSort
FROM Folder f
WHERE f.UserId = @userId
and ParentId = 0
SELECT fChild.Id,fChild.ParentId,fChild.AccountId, fChild.UserId, fChild.Description,fChild.SortOrder, fChild.FolderTypeId, CAST( ‘ ‘ + fChild.Name as nvarchar(max)) as Name,
CAST(CTE.MasterSort + ‘:’ + cast (fChild.SortOrder as nvarchar(max)) as nvarchar(max)) as MasterSort
FROM Folder fChild
ON fChild.ParentId = CTE.Id
Select* from CTE
order by mastersort