Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tbl (id int, parent_id int);
insert into tbl values
(1, 0),
(2, 0),
(3, 1),
(4, 0),
(5, 0),
(6, 0),
(7, 5),
(13, 0),   -- another set do demostrate need for ORDER BY items
(12, 13),
(11, 13);

CREATE TABLE



INSERT 0 10


SELECT child.*
FROM   tbl      AS child
LEFT   JOIN tbl AS parent ON parent.id = child.parent_id
ORDER  BY COALESCE(parent.id, child.id)  -- order by parent if exists
        , parent.id IS NOT NULL          -- parent first per group
        , child.id;                      -- order rest by id
        


id
parent_id


1
0

3
1

2
0

4
0

5
0

7
5

6
0

13
0

11
13

12
13



SELECT 10


-- Without join:
SELECT *
FROM   tbl
ORDER  BY CASE WHEN parent_id = 0 THEN id ELSE parent_id END  -- order by parent if exists
        , parent_id <> 0                                      -- parent first per group
        , id;                                                 -- order rest by id
        


id
parent_id


1
0

3
1

2
0

4
0

5
0

7
5

6
0

13
0

11
13

12
13



SELECT 10


-- a_horse's recrusive query:
with recursive entries as (
  select id, parent_id, id as root_id
  from tbl
  where parent_id = 0 -- this should be IS NULL
  union all 
  select c.id, c.parent_id, p.root_id
  from tbl c
  join entries p on p.id = c.parent_id
)
select id, parent_id
from entries
order by root_id, id;


id
parent_id


1
0

3
1

2
0

4
0

5
0

7
5

6
0

11
13

12
13

13
0



SELECT 10


-- Gordon's query
select *
from   tbl
order  by coalesce(nullif(parent_id, 0), id), id;


id
parent_id


1
0

3
1

2
0

4
0

5
0

7
5

6
0

11
13

12
13

13
0



SELECT 10