How to sort the result by displaying parent id first then all the children id underneath it

Question

I have set of results like this

ID >> Parent
1  >> 0
2  >> 0
3  >> 0
4  >> 1
5  >> 1
6  >> 2
7  >> 1
8  >> 0 
9  >> 3
10 >> 0

I want to sort the record in a way where I display the records by having parent and underneath it all of it children. So the results will look like this

1  >> 0
4  >> 1
5  >> 1
7  >> 1
2  >> 0
6  >> 2
3  >> 0
9  >> 3
8  >> 0 
10 >> 0

How can sort the records in this order using MySQL?

Thanks

Possible duplicate of [MySQL SELECT Tree Parent IDs](http://stackoverflow.com/questions/5688376/mysql-select-tree-parent-ids). — Aiias, Jun 27 '13 at 17:35
I have tried Sort By id, parent that did not work. Also have tried ORDER BY COALESCE(parent, id), parent, id. this did not work — Mike, Jun 27 '13 at 17:50

score 1 · Accepted Answer · answered Jun 27 '13 at 17:51

1

MySQL doesn't support recursive queries. But if a parent can't have a parent (like in your sample data) you could use something like this:

SELECT   *
FROM     mytable
ORDER BY CASE WHEN Parent=0 THEN ID ELSE Parent END, ID

Please see fiddle here.

answered Jun 27 '13 at 17:51

fthiella

48,073
15
90
106

This worked indeed. any known reason why MySQL does not support recursive queries? Thank you so much – Mike Jun 27 '13 at 18:08

score 0 · Answer 2 · answered Jun 27 '13 at 17:54

0

Or maybe...

SELECT id,COALESCE(parent,id) p FROM my_table ORDER BY p,id;

answered Jun 27 '13 at 17:54

Strawberry

33,750
13
40
57

How to sort the result by displaying parent id first then all the children id underneath it

2 Answers2