sql server - Ordering a SELECT - Logical issue (SQL) -

i'm stuck in logical issue , don't know how proceed.

i have 2 columns: id , folderid. folder can subfolder too, want order result first selecting folders has no folderid (root folder), , subfolders , on. way not have problem "folder x doesn't exists".

in example, can´t need simple ordering folderid asc and/or id asc.

the correct result 3rd one:

1. first, id 2 "teste" folder because has folderid 0 = root one.
2. now want "controladoria" folder, because folderid 2, needs folder id 2 created first (teste)
3. "pcp" folder, needs folder id 1 (controladoria)
4. "pasta1" folder, needs folder id 3 (pcp)
5. on , on...

i've tried several ways multiple order , join/left join in same table can´t figure out how can this.

any ideas?

using simple recursive query can these results.

;with cte       (select *,                  1 rn             table1            folderid = 0           union           select t1.*,                  t2.rn + 1             table1 t1                  inner join cte t2                          on t1.folderid = t2.id)  select [id],         [name],         [folderid]    cte  order  rn  

using query can deal multiple subfolders.

take @ working example on sql fiddle.
if want explenation recursive queries, take @ this blog.