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;