If you want the child rows in alphabetical order, you might try to use ORDER BY clause. However, this would break up the hierarchy, and turn it back into a flat list of names. So, Oracle brings you ORDER SIBLINGS BY clause which can be used in hierarchical data to sort data in a specific order without breaking the hierarchical representation.

ORDER SIBLINGS BY: Applies an order to siblings, without altering the basic hierarchical structure of the data returned by the query.

SELECT Ename, Empno, Mgr, Job
  FROM Emp
 START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = Mgr;

ENAME           EMPNO        MGR JOB                                            
---------- ---------- ---------- ---------                                      
KING             7839            PRESIDENT
JONES            7566       7839 MANAGER
SCOTT            7788       7566 ANALYST
ADAMS            7876       7788 CLERK
FORD             7902       7566 ANALYST
SMITH            7369       7902 CLERK
BLAKE            7698       7839 MANAGER
ALLEN            7499       7698 SALESMAN
WARD             7521       7698 SALESMAN
MARTIN           7654       7698 SALESMAN
TURNER           7844       7698 SALESMAN
JAMES            7900       7698 CLERK
CLARK            7782       7839 MANAGER
MILLER           7934       7782 CLERK

14 rows selected.

Q : Write a query to get the hierarchy from EMP table for the grand parent(s) where all subordinates are sorted alphabetically?

Ans:

SELECT Ename, Empno, Mgr, Job
  FROM Emp
 START WITH Mgr IS NULL
CONNECT BY PRIOR Empno = Mgr
ORDER SIBLINGS BY Ename;

ENAME           EMPNO     MGR JOB
---------- ---------- ------- ---------
KING             7839         PRESIDENT
BLAKE            7698    7839 MANAGER
ALLEN            7499    7698 SALESMAN
JAMES            7900    7698 CLERK
MARTIN           7654    7698 SALESMAN
TURNER           7844    7698 SALESMAN
WARD             7521    7698 SALESMAN
CLARK            7782    7839 MANAGER
MILLER           7934    7782 CLERK
JONES            7566    7839 MANAGER
FORD             7902    7566 ANALYST
SMITH            7369    7902 CLERK
SCOTT            7788    7566 ANALYST
ADAMS            7876    7788 CLERK

14 rows selected.

Explaining above query output:

ORDER SIBLINGS BY do not break the hierarchy. It arranges the data for each parent based on the order by column specified.

For “KING” are arranged alphabetically.

BLAKE  7698 7839 MANAGER
CLARK  7782 7839 MANAGER
JONES  7566 7839 MANAGER

Similarly, for each subordinates, child rows are arranged alphabetically. For example, child rows for “BLAKE” are sorted alphabetically.

ALLEN   7499 7698 SALESMAN
JAMES   7900 7698 CLERK
MARTIN  7654 7698 SALESMAN
TURNER  7844 7698 SALESMAN
WARD    7521 7698 SALESMAN

Q : Get all bottom level job categories for MANAGER in each department?

Ans:

SELECT 
       LPAD(Job, length(Job) + 2*LEVEL-1) Job,
       Deptno
  FROM Emp
 START WITH Job = 'MANAGER'
CONNECT BY PRIOR Empno = Mgr;

JOB                      DEPTNO
-------------------- ----------
 MANAGER                     20
   ANALYST                   20
     CLERK                   20
   ANALYST                   20
     CLERK                   20
 MANAGER                     30
   SALESMAN                  30
   SALESMAN                  30
   SALESMAN                  30
   SALESMAN                  30
   CLERK                     30
 MANAGER                     10
   CLERK                     10

13 rows selected.

Clearly the above output contains so many duplicate rows. You know you can remove duplicates by using DISTINCT or GROUP BY. But using these clauses break the hierarchy. Watch…

--Using DISTINCT with CONNECT BY breaks the hierarchy
SELECT DISTINCT
       LPAD(Job, length(Job) + 2*LEVEL-1) Job,
       Deptno
  FROM Emp
 START WITH Job = 'MANAGER'
CONNECT BY PRIOR Empno = Mgr;

JOB                      DEPTNO
-------------------- ----------
     CLERK                   20
 MANAGER                     30
   CLERK                     30
   ANALYST                   20
   SALESMAN                  30
 MANAGER                     20
 MANAGER                     10
   CLERK                     10

8 rows selected.

Though above data is correct but does above output makes sense for all bottom level job categories for MANAGER in each department? Using DISTINCT with CONNECT BY breaks the hierarchy and output may not seem meaningful. So, what’s the solution?

Use DISTINCT to remove duplicates and then sort the rows. ORDER SIBLINGS BY clause retain the hierarchy.

SELECT DISTINCT
       LPAD(Job, length(Job) + 2*LEVEL-1) Job,
       Deptno
  FROM Emp
 START WITH Job = 'MANAGER'
CONNECT BY PRIOR Empno = Mgr
ORDER SIBLINGS BY Empno;

ERROR at line 7:
ORA-01791: not a SELECTed expression


SELECT DISTINCT
       LPAD(Job, length(Job) + 2*LEVEL-1) Job,
       Deptno
  FROM Emp
 START WITH Job = 'MANAGER'
CONNECT BY PRIOR Empno = Mgr
ORDER SIBLINGS BY Job;

ERROR at line 7:
ORA-00976: Specified pseudocolumn or operator not allowed here.


SELECT DISTINCT
       LPAD(Job, length(Job) + 2*LEVEL-1) Job1,
       Deptno
  FROM Emp
 START WITH Job = 'MANAGER'
CONNECT BY PRIOR Empno = Mgr
ORDER SIBLINGS BY LPAD(Job, length(Job) + 2*LEVEL-1);

ERROR at line 7:
ORA-00976: Specified pseudocolumn or operator not allowed here.

So, how to get all bottom level job categories for MANAGER in each department without any duplicates? ORDER BY breaks the hierarchy in data, despite that, you can sometimes experience of the power of subqueries and ORDER BY in situation like this.

SELECT Job, Deptno
  FROM (
        SELECT DISTINCT LEVEL lvl,
               LPAD(Job, length(Job) + 2*LEVEL-1) Job,
               Deptno
          FROM Emp
         START WITH Job = 'MANAGER'
        CONNECT BY PRIOR Empno = Mgr
       )
ORDER By Deptno, lvl;

JOB                      DEPTNO
-------------------- ----------
 MANAGER                     10
   CLERK                     10
 MANAGER                     20
   ANALYST                   20
     CLERK                   20
 MANAGER                     30
   SALESMAN                  30
   CLERK                     30

8 rows selected.