the distinct broken the order siblings by,how can i use them at the same time? such as select distinct * from table xxx starts with ... connect by id=pid order siblings by field here is the test sqls executed with different results

select distinct * from test_table start with pid is null connect by prior id=pid order siblings by order_num;

select * from test_table start with pid is null connect by prior id=pid order siblings by order_num;

here is the table

create table TEST_TABLE(id NUMBER,pid NUMBER,order_num NUMBER);
insert into TEST_TABLE (id, pid, order_num) values (1, null, 1);
insert into TEST_TABLE (id, pid, order_num) values (2, 1, 5);
insert into TEST_TABLE (id, pid, order_num) values (3, null, 2);
insert into TEST_TABLE (id, pid, order_num) values (4, 1, 4);
insert into TEST_TABLE (id, pid, order_num) values (5, 3, 2);

CodePudding user response：

You can use ORDER SIBLINGS BY in an inner query and then use ROW_NUMBER() analytic function to find the duplicates in an outer query and maintain the order using ORDER BY ROWNUM in that outer query:

SELECT id, pid, order_num
FROM   (
  SELECT id, pid, order_num,
         ROW_NUMBER() OVER (PARTITION BY id, pid, order_num ORDER BY ROWNUM) AS rn
  FROM   (
    SELECT id, pid, order_num
    FROM   test_table
    START WITH pid IS NULL
    CONNECT BY PRIOR id = pid
    ORDER SIBLINGS BY order_num
  )
  ORDER BY ROWNUM
)
WHERE  rn = 1

Which, for the sample data:

create table TEST_TABLE(id,pid,order_num) AS
SELECT 1, NULL, 1 FROM DUAL UNION ALL
SELECT 2, 1, 5 FROM DUAL UNION ALL
SELECT 3, NULL, 2 FROM DUAL UNION ALL
SELECT 4, 1, 4 FROM DUAL UNION ALL
SELECT 5, 3, 2 FROM DUAL UNION ALL
SELECT 1, 5, 5 FROM DUAL;

Note: this has an added row so there is a path back to a previous branch in the hierarchy to create duplicate rows in the output.

Outputs:

ID	PID	ORDER_NUM
1	null	1
4	1	4
2	1	5
3	null	2
5	3	2
1	5	5

and maintains the order of the siblings from the hierarchical query.

db<>fiddle here