In Hierarchical queries using the Oracle RDBMS – Determine ordering within each level, I showed how to sort the rows of a hierarchical SQL query within each tree level by using ORDER SIBLINGS BY instead of ORDER BY. There is one detail to keep in mind, though: even the ORDER SIBLINGS BY is done on the overall result of the query, which can cause problems when using UNION ALL to take additional rows into the result:
SELECT 0, 0, 'xyz' FROM DUAL
UNION ALL
SELECT LEVEL, Id, substr( LPAD (' ', 3 * (LEVEL - 1)) || Label, 0, 30) Node
FROM TreeNode
START WITH Parent = 0
CONNECT BY PRIOR Id = Parent
ORDER SIBLINGS BY Label;
ERROR at line 7: ORA-30929: ORDER SIBLINGS BY clause not allowed here
Using parantheses to explicitly group the second select within the UNION ALL does not help, but there is a simple solution: just wrap the hierarchical query into a sub select, and then do a SELECT * from the sub select:
SELECT 0, 0, 'xyz' FROM DUAL
UNION ALL
SELECT * FROM (
SELECT LEVEL, Id, substr( LPAD (' ', 3 * (LEVEL - 1)) || Label, 0, 30) Node
FROM TreeNode
START WITH Parent = 0
CONNECT BY PRIOR Id = Parent
ORDER SIBLINGS BY Label
);
0 0 'XYZ'
------------------ ------------------ ---------
0 0 xyz
1 1 Root Node
2 2 Node 1
3 3 Node 1.1
3 4 Node 1.2
...