Sql question: Getting Parent rows followed by child rows

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

first parent

all children of first parent

second parent

all children of second parent

third parent

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

You'd use a recursive CTE for this:

WITH r AS 
 (SELECT id, 
    NULL AS parent_id, 
    CAST(right('000' + CAST(row_number() 
         OVER (table.id) AS varchar), 3) AS varchar(50))
  FROM table WHERE parent IS NULL

  UNION ALL

  SELECT table.id, table.parent_id, 
    CAST(r.ord + right('000' + CAST(row_number() 
         OVER (ORDER BY table.id) AS varchar), 3) AS varchar(50))
  FROM r JOIN table 
   ON table.parent = r.id)

 SELECT id 
 FROM r
 ORDER BY left(ord + '000000000000000000000000000000000', 36)

Note that this particular version will break if any ID has a value greater than 999, and it will break if you have more than 12 levels. If this is a concern to you, you need to adjust the number of zeroes in the various places.

There might be better ways, but this one works.