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 ...