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?
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 groupid
: Finally sort all the children (same parent, and parent
is not null)If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With