Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySql: ORDER BY parent and child

Tags:

I have a table like:

+------+---------+- | id   | parent  | +------+---------+ | 2043 |    NULL | | 2044 |    2043 | | 2045 |    2043 | | 2049 |    2043 | | 2047 |    NULL | | 2048 |    2047 | | 2049 |    2047 | +------+---------+ 

which shows a simple, 2-level "parent-child"-corelation. How can I ORDER BY an SELECT-statement to get the order like in the list above, which means: 1st parent, childs of 1st parent, 2nd parent, childs of 2nd parent and so on (if I have that, I can add the ORDER BYs for the children... I hope). Is it possible withoug adding a sort-field?

like image 519
Werner Avatar asked Nov 14 '12 15:11

Werner


1 Answers

Including sorting children by id:

ORDER BY COALESCE(parent, id), parent IS NOT NULL, id 

SQL Fiddle example

Explanation:

  • COALESCE(parent, id): First sort by (effectively grouping together) the parent's id.
  • parent IS NOT NULL: Put the parent row on top of the group
  • id: Finally sort all the children (same parent, and parent is not null)
like image 153
lc. Avatar answered Oct 03 '22 00:10

lc.