How to Order Siblings in Hierarchical Queries

In a hierarchical query, if you specify either ORDER BY or GROUP BY, the hierarchical order will be destroyed. If we need to sort the rows of siblings of the the same level (while keeping the hierarchical order) we can to use ORDER SIBLINGS BY clause. Let’s create a sample table:

CREATE TABLE emp ( emp_id NUMBER, name VARCHAR2(50),  manager_id NUMBER );
INSERT INTO emp VALUES ( 1, 'JACK', null ); 
INSERT INTO emp VALUES ( 2, 'MILLS', 1 );
INSERT INTO emp VALUES ( 3, 'ATIL', 1 ); 
INSERT INTO emp VALUES ( 4, 'BROWN', 3 ); 
INSERT INTO emp VALUES ( 5, 'ROGERS', 2 ); 
INSERT INTO emp VALUES ( 6, 'DAVIDS', 2 ); 
INSERT INTO emp VALUES ( 7, 'LEE', 3 ); 
INSERT INTO emp VALUES ( 8, 'WHITE', 2 ); 
COMMIT;

CREATE TABLE emp ( emp_id NUMBER, name VARCHAR2(50), manager_id NUMBER );

INSERT INTO emp VALUES ( 1, 'JACK', null );

INSERT INTO emp VALUES ( 2, 'MILLS', 1 );

INSERT INTO emp VALUES ( 3, 'ATIL', 1 );

INSERT INTO emp VALUES ( 4, 'BROWN', 3 );

INSERT INTO emp VALUES ( 5, 'ROGERS', 2 );

INSERT INTO emp VALUES ( 6, 'DAVIDS', 2 );

INSERT INTO emp VALUES ( 7, 'LEE', 3 );

INSERT INTO emp VALUES ( 8, 'WHITE', 2 );

COMMIT;

Let’s list these rows in a hierarchical order:

SELECT LEVEL, LPAD (' ', (LEVEL - 1) * 3) || NAME report
FROM emp
START WITH manager_id IS NULL
CONNECT BY manager_id = PRIOR emp_id;

     LEVEL REPORT
---------- ------------------------------
         1 JACK
         2    MILLS
         3       ROGERS
         3       DAVIDS
         3       WHITE
         2    ATIL
         3       BROWN
         3       LEE

8 rows selected.

SELECT LEVEL, LPAD (' ', (LEVEL - 1) * 3) || NAME report

FROM emp

START WITH manager_id IS NULL

CONNECT BY manager_id = PRIOR emp_id;

LEVEL REPORT

---------- ------------------------------

1 JACK

2 MILLS

3 ROGERS

3 DAVIDS

3 WHITE

2 ATIL

3 BROWN

3 LEE

8 rows selected.

If we want to order the employees at the same level according to their names, we can use siblings keyword:

SELECT level, LPAD (' ', (LEVEL - 1) * 3) || NAME report
FROM emp
START WITH manager_id IS NULL
CONNECT BY manager_id = PRIOR emp_id
order siblings by name;

     LEVEL REPORT
---------- ------------------------------
         1 JACK
         2    ATIL
         3       BROWN
         3       LEE
         2    MILLS
         3       DAVIDS
         3       ROGERS
         3       WHITE

8 rows selected.

SELECT level, LPAD (' ', (LEVEL - 1) * 3) || NAME report

FROM emp

START WITH manager_id IS NULL

CONNECT BY manager_id = PRIOR emp_id

order siblings by name;