Sql Query: Fetch Ordered Rows From A Table - Ii

Following are some entries from a table: id r_id a_id p_id 1 9 9 0 2 9 105 108 3 9 102

Solution 1:

Modifying the answer to your previous question, gives the following...

WITHRECURSIVE sub(s_id, s_r_id, s_a_id, s_p_id, row) AS (
    SELECT id, r_id, a_id, p_id, 1ASrowFROM foo WHERE p_id =0UNIONALLSELECT id, r_id, a_id, p_id, (row+1)  FROM foo JOIN sub ON s_a_id = p_id AND s_r_id = r_id
)
SELECT*FROM sub ORDERBY s_r_id, row;

Solution 2:

Just change the ORDER BY:

WITHRECURSIVE sub(s_id, s_r_id, s_a_id, s_p_id, row) AS (
    SELECT id, r_id, a_id, p_id, 1ASrowFROM foo WHERE p_id =0UNIONALLSELECT id, r_id, a_id, p_id, (row+1)  FROM foo JOIN sub ON s_a_id = p_id
)
SELECT*FROM sub
ORDERBY s_r_id ASC, rowASC
;