I am using the WITH RECURSIVE query to generate a hierarchy of users. The data and the query used is shared in https://www.db-fiddle.com/f/oTbCcMQb5a4B6V96DAYJHG/0 . The query is returning the results correctly, but the order of the data is different from what i need.
I have tweaked the query a few times, but unable to achieve the required result.
Actual Result of the query
+-----+--------+------------+------------+
| id | title | parent_id | sort_order |
+-----+--------+------------+------------+
| 1 | null | null | 0 |
| 2 | A | 1 | 1 |
| 3 | B | 1 | 2 |
| 4 | AA | 2 | 1 |
| 5 | AB | 2 | 2 |
| 13 | BA | 3 | 1 |
| 17 | BC | 3 | 2 |
| 6 | AAA | 4 | 1 |
| 7 | AAB | 4 | 2 |
| 8 | AAC | 4 | 3 |
| 9 | ABA | 5 | 1 |
| 10 | ABB | 5 | 2 |
| 11 | ABC | 5 | 3 |
| 12 | ABD | 5 | 4 |
| 16 | BAA | 13 | 1 |
+-----+--------+------------+------------+
Result I am trying to achieve
+-----+--------+------------+------------+
| id | title | parent_id | sort_order |
+-----+--------+------------+------------+
| 1 | null | null | 0 |
| 2 | A | 1 | 1 |
| 4 | AA | 2 | 1 |
| 6 | AAA | 4 | 1 |
| 7 | AAB | 4 | 2 |
| 8 | AAC | 4 | 3 |
| 5 | AB | 2 | 2 |
| 9 | ABA | 5 | 1 |
| 10 | ABB | 5 | 2 |
| 11 | ABC | 5 | 3 |
| 12 | ABD | 5 | 4 |
| 3 | B | 1 | 2 |
| 13 | BA | 3 | 1 |
| 16 | BAA | 13 | 1 |
| 17 | BC | 3 | 2 |
+-----+--------+------------+------------+
In my query the WITH RECURSIVE will take all nodes of level 1, then it will take all level2 and continues..
But the result I am trying to achieve is take first node of first level, complete the hierarchy, the take second element of the first level and recurse through rest.
Is is possible to get this result using WITH RECURSIVE
The data in the title is sample data, so that it is easy to understand the hierarchy.
order by title asc