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         9
4       9           106         105
5       9           108         102
6       10           10         0
7       10           15         18
8       10           12         10
9       10           16         15
10      10           18         12

I'm looking for an SQL query that will give an output like:

1       9           9           0
3       9           102         9
5       9           108         102
2       9           105         108
4       9           106         105
6       10           10          0
8       10           12         10
10      10           18         12
7       10           15         18
9       10           16         15

Well, I asked a similar question here but the question was not complete and I also got few excellent answers. Editing that question might make the answers unacceptable, so I did not edit and added this as a new question here.

• The root item has a p_id = 0
• For one r_id there can only be one p_id = 0
• The table shown on which Query need to be run may not 开发者_如何学Cbe sorted with respect to root.
• I'm looking things to work in PostgreSql

EDIT: The idea is to sort the rows in such a way that a row with {r_id, p_id} = x should come below the row with {r_id, a_id} = x.

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

WITH RECURSIVE sub(s_id, s_r_id, s_a_id, s_p_id, row) AS (
    SELECT id, r_id, a_id, p_id, 1 AS row FROM foo WHERE p_id = 0
UNION ALL
    SELECT 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 ORDER BY s_r_id, row;

Just change the ORDER BY:

WITH RECURSIVE sub(s_id, s_r_id, s_a_id, s_p_id, row) AS (
    SELECT id, r_id, a_id, p_id, 1 AS row FROM foo WHERE p_id = 0
UNION ALL
    SELECT id, r_id, a_id, p_id, (row + 1)  FROM foo JOIN sub ON s_a_id = p_id
)
SELECT * FROM sub
ORDER BY s_r_id ASC, row ASC
;