How to order data in mysql as given

Question

How to Sort this table rows such that next row pid isequal to prev row id

id	name	pid
1	row1	NULL
2	row2	5
3	row3	2
4	row4	1
5	row5	4
6	row6	3

to

id	name	pid
1	row1	NULL
4	row4	1
5	row5	4
2	row2	5
3	row3	2
6	row6	3

user_10138734 · Answer

WITH RECURSIVE
cte AS ( SELECT id, name, pid, 1 level
         FROM table
         WHERE pid IS NULL
       UNION ALL
         SELECT table.id, table.name, table.pid, cte.level + 1
         FROM table
         JOIN cte ON table.pid = cte.id )
SELECT id, name, pid
FROM cte
ORDER BY level

How to order data in mysql as given

1 Answers