Ordering the siblings v16

You can order the result set so the siblings appear in ascending or descending order by selected column values using the ORDER SIBLINGS BY clause. This is a special case of the ORDER BY clause that you can use only with hierarchical queries.

You can further modify the previous query by adding 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 don't satisfy the WHERE clause were 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)