SQL order by grouping children of the same parent (one generation)

I am assuming there is only a generation, so I want to order the result from a db table with (id,parent_id) structure implementing the parent-child internal relationship, ie:


Table name: catogories
id parent_id name
int int varchar(100)




So, I found some hint here, and here, but actually I did wrote my solution first using IF in SQL:

SELECT c.*, IF(c.parent_id = 0, c.id*1000, c.parent_id*1000+c.id) as sorter FROM categories as c ORDER BY sorter

And for one generation structure, it works. Results are something like:

id parent_id name sorter
1 0 Fruits 1001
3 1 Banana 1003
2 0 Vegetables 2002


That look simple enough


Note on image source: flickr vivariphotography, CC, commercial use allowed, link