Hierarchical SQL Query that sorts items properly

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.

<pre>

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

UNIONALL

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

JOIN CTE

ON fChild.ParentId = CTE.Id

)

Select* from CTE

order by mastersort

</pre>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s