techytee techytee - 5 months ago 10
SQL Question

Select parents and children from one table in the correct order

I have a table that has hierarchical data with parent and child records. I want to select them in the correct order no matter which order the data recorded in the table using MySql. How to list the following data in below example using a single query?

Ex: Table

ID | Title | Parent
1 | A | 0
2 | B | 1
3 | C | 1
4 | D | 0
5 | E | 4
6 | F | 4
7 | G | 0
8 | H | 7
9 | I | 1
10 | J | 4


Into..

ID | Title | Parent
1 | A | 0
2 | B | 1
3 | C | 1
9 | I | 1
4 | D | 0
5 | E | 4
6 | F | 4
10 | J | 4
7 | G | 0
8 | H | 7

Answer

You can try using CASE EXPRESSION in the ORDER BY clause :

SELECT t.*
FROM YourTable t
ORDER BY CASE WHEN t.Parent = 0 THEN t.ID ELSE t.Parent END,
         t.ID

Note: This will work for the data you provided, it won't work for more then 1 level of hierarchy !

Comments