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