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);
Advertisement
Answer
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