Hi

Jeff Moden has this excellent article on hierarchies

http://www.sqlservercentral.com/articles/Hierarchy/94040/

I suspect the creating a hybrid sort path will help you with the ordering.

;with Menu as (

SELECT * FROM (

VALUES

('100','Main Menu','',1)

,('200','Sales Management','100',1)

,('300','Customer Relationship Management','200',1)

,('400','Setup','300',1)

,('500','Attribute','400',1)

,('515','Call Type','400',2)

,('504','Competitor','400',3)

,('410','General Operations','300',2)

,('521','Customer/Contact Import','410',1)

,('550','Mobile Connect Conflicts','410',2)

,('560','Mobile Connect Sync','410',3)

,('305','Help Desk','200',2)

) AS MenuTable(MenuID, MenuDesc,ParentMenuID, Seq)

)

, cte as (

SELECT MenuID,MenuDesc,ParentMenuID,Seq, 1 as Lev

, cast(cast(seq as varchar(10)) + ':' + menuid as varchar(MAX)) sortPathChar

from Menu

where ParentMenuID = ''

UNION ALL

select m.MenuID,m.MenuDesc,m.ParentMenuID,m.Seq,Lev+1

, sortPathChar + '/' + cast(m.seq as varchar(10)) + ':' + m.menuid sortPathChar

from cte

join Menu m

on m.ParentMenuID=cte.MenuID

)

SELECT * FROM cte

ORDER BY sortPathChar

For your purpose I don't think you need the menuID in it, but i put it in for clarity

Fixed link