sql - How to order rows by hierarchy -

i have table hierarchical, parent-child relations , want order hierarchy. table is:

id|parent|type -------------- 1 |0     |1 2 |0     |1 3 |0     |1 4 |0     |2 5 |0     |2 6 |2     |2 7 |3     |2 

and result want this:

id|parent|type -------------- 1 |0     |1 2 |0     |1 6 |2     |2 3 |0     |1 7 |3     |2 4 |0     |2 5 |0     |2 

so want tree view type 1 ordered first , type 2 @ end.

now i'm trying use recursion order wrong:

with cte (   select id, parent, type tbl id=1   union   select id, parent, type,   row_number()over(    order          (case when t.type = 1 1             when t.type = 2 2     else 1000     end) rn   tbl t   inner join cte c on c.id=t.parent ) select * cte order rn 

how can this?

using order hierarchyid cte simple, not test recursive relations

declare @data table (id int identity(1,1) primary key, parent int, type int)  insert @data values  (0, 1), (0, 1), (0, 1), (0, 2), (0, 2), (2, 2), (3, 2)  select * @data  ;with level (     -- root, build hierarchy /{type}.{id}/, type important id     select *, -- 0 level,         '/' + convert(varchar(max), type + 0.1 * id) + '/' ordering      @data      parent = 0     union     -- connect parent appending hierarchy     select d.*, -- c.level + 1,          c.ordering + convert(varchar(max), d.type + 0.1 * d.id) + '/'      @data d inner join level c on d.parent = c.id ) select id, parent, type level  order cast(ordering hierarchyid) -- key part convert data type 

sql fiddle