Ordering the Siblings v12
The result set can be ordered so the siblings appear in ascending or descending order by selected column value(s) using the ORDER SIBLINGS BY clause. This is a special case of the ORDER BY clause that can be used only with hierarchical queries.
The previous query is further modified with the addition of ORDER SIBLINGS BY ename ASC.
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;The output from the prior query is now modified so the siblings appear in ascending order by name. Siblings BLAKE, CLARK, and JONES are now alphabetically arranged under KING. Siblings ALLEN, JAMES, MARTIN, TURNER, and WARD are alphabetically arranged under BLAKE, and so on.
level | employee | empno | mgr ------+-------------+-------+------ 1 | KING | 7839 | 2 | BLAKE | 7698 | 7839 3 | ALLEN | 7499 | 7698 3 | JAMES | 7900 | 7698 3 | MARTIN | 7654 | 7698 3 | TURNER | 7844 | 7698 3 | WARD | 7521 | 7698 2 | CLARK | 7782 | 7839 3 | MILLER | 7934 | 7782 2 | JONES | 7566 | 7839 3 | FORD | 7902 | 7566 4 | SMITH | 7369 | 7902 3 | SCOTT | 7788 | 7566 4 | ADAMS | 7876 | 7788 (14 rows)
This final example adds the WHERE clause and starts with three root nodes. After the node tree is constructed, the WHERE clause filters out rows in the tree to form the result set.
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr
FROM emp WHERE mgr IN (7839, 7782, 7902, 7788)
START WITH ename IN ('BLAKE','CLARK','JONES')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;The output from the query shows three root nodes (level one) - BLAKE, CLARK, and JONES. In addition, rows that do not satisfy the WHERE clause have been eliminated from the output.
level | employee | empno | mgr ------+-----------+-------+------ 1 | BLAKE | 7698 | 7839 1 | CLARK | 7782 | 7839 2 | MILLER | 7934 | 7782 1 | JONES | 7566 | 7839 3 | SMITH | 7369 | 7902 3 | ADAMS | 7876 | 7788 (6 rows)