Parent - Child sql query

Question

id  parent_id
1   0
2   0
3   2
4   0
5   1
6   0

I need a query that will return parent rows (parent_id=0) followed by its child rows:

1. first parent
2. all children of first parent
3. second parent
4. all children of second parent
5. third parent
6. fourth parent

Expected result: ordered by id

id   parent_id
-------------------------------------------
1    0 (first parent)
5    1     (all children of first parent)
2    0 second parent
3    2     (all children of second parent)
4    0 third parent
6    0 fourth parent

I can use union of parents followed by all childs But that gives me parents first then the children. I need parent and immediately its children.

Anyone can help?

Answer 1

If you're in SQL Server 2005+, you can use a recursive CTE, making sure that you maintain a field that you can order by at the end.

Try this:

declare @t table (id int, parent_id int)
insert @t
select 1,0
union all select 2,0
union all select 3,2
union all select 4,0
union all select 5,1
union all select 6,0
;

with tree as (
select t.*, convert(varbinary(max),t.id) as ordered
from @t t
where parent_id = 0
union all
select t.*, ordered + convert(varbinary(max),t.id)
from tree base
 join
 @t t
 on t.parent_id = base.id
 )
select * 
from tree
order by ordered
;