Search code examples
postgresqloracle11ghierarchical-query

How to maintain the displayorder of oracle and postgreSQL?

I want the same display order in postgreSQL as generated in oracle output. I have tried using the following oracle query and output shown below

create table student(
std_id int, std_roll int
);


SELECT std_id, std_roll
FROM   student
CONNECT BY PRIOR std_id = std_roll;

oracle output

Oracle output

Corresponding postgreSQL query and output

create table student(
std_id int, std_roll int
);


INSERT into student values( 1, 0 );
INSERT into student values( 2, 1 );
INSERT into student values( 3, 1 );
INSERT into student values( 4, 2 );
INSERT into student values( 5, 2 );
INSERT into student values( 6, 3 );
INSERT into student values( 7, 4 );

 WITH RECURSIVE q AS (
 SELECT po.std_id,po.std_roll
 FROM student po
 UNION ALL
 SELECT po.std_id,po.std_roll
 FROM student po
 JOIN q ON q.std_id=po.std_roll
 )
 SELECT * FROM q;

postgreSQL output

postgreSQL output

Is it possible to maintain the same display order in postgreSQL as generated in oracle ?

Solution

  • From the data you posted, it seems that you need, in Oracle, ORDER SIBLINGS.

    With a table like yours:

    create table student(
std_id int, std_roll int
);
INSERT into student values( 1, 0 );
INSERT into student values( 2, 1 );
INSERT into student values( 3, 1 );
INSERT into student values( 4, 2 );
INSERT into student values( 5, 2 );
INSERT into student values( 6, 3 );
INSERT into student values( 7, 4 );

    this gives the output you need:

    SELECT std_id, std_roll, level
FROM   student
CONNECT BY PRIOR std_id = std_roll
order siblings by std_id

    In Postgres, you should be able to get the same result by using this answer