MySQL Select parents and childs in proper order with single query

Question

I have a MySQL table with following data:

ID Name      ParentID
1  Foo       null
2  Bar       null
3  Foo SubA  1
4  Bar SubA  2
5  Foo SubC  1
6  Foo SubB  1

I would like to retreive all data with following order:

1  Foo       null
3  Foo SubA  1
6  Foo SubB  1
5  Foo SubC  1
2  Bar       null
4  Bar SubA  2

Is it possible with MySQL and single query?

Answer 1

If this is a two-level hierarchie, i.e. no grandparents and grandchildren, it's a mere ORDER BY clause:

select id, name, parentid
from mytable
order by coalesce(parentid, id), parentid is not null, name;

This makes use of MySQL's true = 1, false = 0. parentid is not null is 0 for the parent and 1 for the children.