Defining the sort order of children in a hierarchy query
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
/
SQL Server has a hierarchyID type that handles this very well. For all other RDBMS, I normally use a string concat emulation as below.
select task_id
,sequence_within_parent
,lpad(' ', 2 * (level - 1), ' ') || task task
,SYS_CONNECT_BY_PATH(
to_char(parent_id, 'FM000000000')
||
to_char(sequence_within_parent, 'FM000000000')
,'/') hier
from tasks
connect by parent_id = prior task_id
start with task_id = 1
order by hier;