sql server - SQL Parent Child Tree Sort BY DEPTH and Display Order -

i have query creates hierarchy of categories. works great. i'm trying control display order based on depth. example have category called 'cat'. has 2 sub categories called 'sub cat 1' , 'sub cat 2'. possible sort sub categories display order , depth? order ‘sub cat 2’ ‘sub cat 1’, based on display order. want able control order of categories based on location in depth , display order.

here query:

declare @categoryid int;  declare @excludeinactive bit;   set @categoryid = 2;  set @excludeinactive = 0;  declare @categories table (     id int,     name varchar(500),     parentid int,     depth int,      active bit,     displayorder int );  insert @categories select 2, 'main', 0, 0, 1, 0 union select 6, 'cat', 2, 0, 1, 0 union select 13, 'sub cat 1', 6, 1, 1, 2 union select 14, 'sub cat 2', 6, 1, 1,    1 union select 5, 'cat 2', 2,0, 1, 0 union select 15, 'sub cat 1', 5, 1, 1, 2 union select 16, 'sub cat 2', 5, 1, 1, 1;  tree (id, name, parentid, depth, sort, active, displayorder)  (      select id, name, parentid, 0 depth, convert(varchar(255), name) sort, active,     displayorder              @categories      parentid = @categoryid      union      select ct.id, ct.name, ct.parentid, parent.depth + 1 depth,      convert(varchar(255), parent.sort + ' > ' + ct.name) sort, ct.active, ct.displayorder      @categories ct      inner join tree parent on parent.id = ct.parentid (@excludeinactive = 0  or (ct.active = 1 ))    )    select id, name, parentid, depth, sort, active, displayorder tree order sort 

current output:

id  name       parentid   depth  sort             active    displayorder 6   cat        2          0      cat                  1         2 13  sub cat 1  6          1      cat > sub cat 1      1         2 14  sub cat 2  6          1      cat > sub cat 2      1         1 5   cat 2      2          0      cat 2                1         1 15  sub cat 1  5          1      cat 2 > sub cat 1    1         2 16  sub cat 2  5          1      cat 2 > sub cat 2    1         1 

desired output:

id  name       parentid   depth  sort             active    displayorder 5   cat 2      2          0      cat 2                1         1 16  sub cat 2  5          1      cat 2 > sub cat 2    1         1 15  sub cat 1  5          1      cat 2 > sub cat 1    1         2 6   cat        2          0      cat                  1         2 14  sub cat 2  6          1      cat > sub cat 2      1         1 13  sub cat 1  6          1      cat > sub cat 1      1         2 

there's more elegant way this, like:

declare @categoryid int;  declare @excludeinactive bit;   set @categoryid = 2;  set @excludeinactive = 0;  declare @categories table (     id int,     name varchar(500),     parentid int,     depth int,      active bit,     displayorder int );  insert @categories select 2, 'main', 0, 0, 1, 0 union select 6, 'cat', 2, 0, 1, 0 union select 13, 'sub cat 1', 6, 1, 1, 2 union select 14, 'sub cat 2', 6, 1, 1,    1 union select 5, 'cat 2', 2,0, 1, 0 union select 15, 'sub cat 1', 5, 1, 1, 2 union select 16, 'sub cat 2', 5, 1, 1, 1;  tree (id, name, parentid, depth, sort, active, displayorder)  (      select id, name, parentid, 0 depth, convert(varchar(255), name) sort, active,     displayorder              @categories      parentid = @categoryid      union       select ct.id, ct.name, ct.parentid, parent.depth + 1 depth,      convert(varchar(255), parent.sort + ' > ' + ct.name) sort, ct.active, ct.displayorder      @categories ct      inner join tree parent on parent.id = ct.parentid (@excludeinactive = 0  or (ct.active = 1 ))    )   select id, name, parentid, depth, sort, active, displayorder  tree  order substring(sort,1,charindex(' >',sort+' >')) desc, displayorder 

note, unions should all unable post them due firewall quirk.