Defining the sort order of children in a hierarchy query Defining the sort order of children in a hierarchy query oracle oracle

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;