CODEWITHSUNDEEP
mysqlmariadb
a_k
a_k

Reputation: 84

How to order data in mysql as given

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

Upvotes: 2

Views: 46

Answers (1)

Akina
Akina

Reputation: 42622

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

Upvotes: 1

Related Questions