SQL Query: Fetch ordered rows from a table

Question

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

Is it possible to get the following output using SQL query

1       9           9           0
3       9           102         9
5       9           108         102
2       9           105         108
4       9           106         105

The idea is to sort the rows in such a way that a row with p_id = x should come below the row with a_id = x.

I hope question makes sense.

Regards,
Mayank

EDIT:
I'm looking this for PostgreSql

The root item has a p_id = 0
There are no missing links

Frank Heikens · Accepted Answer · 2011-05-18 07:32:43Z

1

Use a recursive query (PostgreSQL version 8.4 or later):

/* test data:
CREATE TABLE foo (id, r_id, a_id, p_id) AS
    SELECT  1,9,9,0
    UNION ALL SELECT 2,9,105,108
    UNION ALL SELECT 3,9,102,9
    UNION ALL SELECT 4,9,106,105
    UNION ALL SELECT 5,9,108,102        
;
*/

-- the query you need:
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 row;

answered May 18, 2011 at 7:32

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mayank Over a year ago

Thanks a lot. However, just need an advice regarding performance. How much better/worse would it be to fetch all the values and then sort it in memory?

Frank Heikens Over a year ago

Use EXPLAIN ANALYZE to see how the query is executed and how much memory is used. You might need some changes in the settings for work_mem, depending on your situation.

Lieven Keersmaekers · Accepted Answer · 2011-05-18 08:52:30Z

1

Following is adapted from a working SQL Server 2005 solution.

I have made some assumptions

The root item has a p_id = 0
There are no missing links

SQL Statement

;WITH RECURSIVE q AS (
    SELECT  *
            , 1 AS Level
    FROM    ATable 
    WHERE   p_id = 0
    UNION ALL
    SELECT  t.*
            , Level = Level + 1
    FROM    q
            INNER JOIN ATable t ON t.p_id = q.a_id          
)
SELECT  *
FROM    q
ORDER BY
        Level

edited May 18, 2011 at 8:52

answered May 18, 2011 at 7:01

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

5 Comments

Johann Blais Over a year ago

The OP edited the question, he is looking for an answer compatible with a postgreSQL database. I won't downvote since you answered before the edit, but please consider editing your answer.

Lieven Keersmaekers Over a year ago

@Johann - I have never looked at postgreSQL but looking at the accepted answer and mine, you hardly notice any differences (The Recursive seems to be the only one). OP should have been able to work that out by himself.

Mayank Over a year ago

Both the answers are acceptable. By the time I realized how it could be done using postgresql I got another answer. Now, I could accept only one answer :).

Johann Blais Over a year ago

I agree but the fact is you explicitly say it is for SQL Server, you might want to remove that restriction if you are confident it works also for PSQL.

Lieven Keersmaekers Over a year ago

@Johan, @Mayank - I believe this would be all it takes. OP already got an answer but he could verify if it works.

Collectives™ on Stack Overflow

SQL Query: Fetch ordered rows from a table

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related