2

I have a mysql table structured like this (removed other columns, not relevant to the question):

id          parent_id
----------- -----------
1           0          
2           0            
3           0          
4           3          
5           2
6           1
7           1

A "parent" is a row that has parent_id = 0.

I would like to know if there is a way to formulate the query so that MySQL outputs the rows with each children right after its parents?

The intended result would be

id          parent_id
----------- -----------
1           0          
6           1            
7           1          
2           0          
5           2          
3           0          
4           3

Thank you for your time.

pixeline
  • 17,669
  • 12
  • 84
  • 109

1 Answers1

4

MySQL does not support recursive queries, so if a child can have childs there's no way to formulate such a query. Otherwise this query should return the rows in the order that you need:

SELECT * FROM tablename
ORDER BY
  CASE WHEN parent_id=0 THEN id ELSE parent_id END,
  id

Please see fiddle here. The trick is to order rows by id if it's a parent or by parent_id if it's a child, and then by id.

fthiella
  • 48,073
  • 15
  • 90
  • 106