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 taskfrom tasksconnect by parent_id = prior task_idstart with task_id = 1order 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') ,'/') hierfrom tasksconnect by parent_id = prior task_idstart with task_id = 1order by hier;