How to maintain the displayorder of oracle and postgreSQL?

Question

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

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

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

For the Postgres part you only posted an insert, not the select you use to get your data; in the Oracle query, you have no ORDER BY clause, so you are not really ordering. Please try to clarify your question and post your complete code. Also, please post data as formatted text, [not screenshots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) — Aleksej, Jan 04 '17 at 12:14
Without an `order by` the order of rows is not defined. You need to find a way to sort the rows to get a guaranteed sort order. — , Jan 04 '17 at 12:39
Possible duplicate of [How to convert oracle hierarchical queries to postgresql?](http://stackoverflow.com/questions/41302331/how-to-convert-oracle-hierarchical-queries-to-postgresql) — Patrick, Jan 04 '17 at 12:51
Can you please tell me how to sort these two outputs to appear in same order — neo, Jan 04 '17 at 12:55
As said, if you want an ordered result, you have to add an `order by`. So you have to decide what is the ordering you need and then add an appropriate order `by clause`. — Aleksej, Jan 04 '17 at 12:58

score 1 · Accepted Answer · edited May 23 '17 at 12:08

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

How to maintain the displayorder of oracle and postgreSQL?

1 Answers1