I would like to know the Oracle SQL query that orders the children in a hierarchy query by the sequence_within_parent column.
An example data set and query are:
create table tasks (task_id number ,parent_id number ,sequence_within_parent number ,task varchar2(30) ); insert into tasks values ( 1, NULL, 0, 'Task 1'); insert into tasks values ( 2, 1, 1, 'Task 1.1'); insert into tasks values ( 3, 1, 2, 'Task 1.2'); insert into tasks values ( 4, 2, 2, 'Task 1.1.2'); insert into tasks values ( 5, 3, 1, 'Task 1.2.1'); insert into tasks values ( 6, 2, 1, 'Task 1.1.1'); insert into tasks values ( 7, 3, 4, 'Task 1.2.4'); insert into tasks values ( 8, 3, 2, 'Task 1.2.2'); insert into tasks values ( 9, 3, 3, 'Task 1.2.3'); insert into tasks values (10 , 2, 3, 'Task 1.1.3'); column task format a30 select task_id ,sequence_within_parent ,lpad(' ', 2 * (level - 1), ' ') || task task from tasks connect by parent_id = prior task_id start with task_id = 1 /
This query returns the following:
TASK_ID SEQUENCE_WITHIN_PARENT TASK ---------- ---------------------- --------------- 1 0 Task 1 2 1 Task 1.1 4 2 Task 1.1.2 6 1 Task 1.1.1 10 3 Task 1.1.3 3 2 Task 1.2 5 1 Task 1.2.1 7 4 Task 1.2.4 8 2 Task 1.2.2 9 3 Task 1.2.3
The preferred output is below where the children are in the correct order:
TASK_ID SEQUENCE_WITHIN_PARENT TASK ---------- ---------------------- --------------- 1 0 Task 1 2 1 Task 1.1 6 1 Task 1.1.1 4 2 Task 1.1.2 10 3 Task 1.1.3 3 2 Task 1.2 5 1 Task 1.2.1 8 2 Task 1.2.2 9 3 Task 1.2.3 7 4 Task 1.2.4
Advertisement
Answer
The clause to be added to the query is “ORDER SIBLINGS BY SEQUENCE_WITHIN_PARENT”.
In the hierarchy all child nodes, or children, are referred to as siblings.
The full query for the example dataset is:
select rownum ,task_id ,sequence_within_parent ,lpad(' ', 2 * (level - 1), ' ') || task task from tasks connect by parent_id = prior task_id start with task_id = 1 order siblings by sequence_within_parent /