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;

LEVEL REPORT

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

1 JACK

2 ATIL

3 BROWN

3 LEE

2 MILLS

3 DAVIDS

3 ROGERS

3 WHITE

8 rows selected.

Please share

5 Comments

Sanjay

March 13, 2014 Reply

Very nice explanation. Very useful for me. Thanks.

Ajesh Rajan

March 16, 2016 Reply

Gokhan Atil

March 16, 2016

SIDDHARTHA PENCHALA

November 12, 2016 Reply

Very Good Explanation…Thank You So Much……
Vamsi

January 2, 2017 Reply

Hi

I have a SQL query with UNION ALL operator.

The Second Select statement in the UNION ALL has the SYS_CONNECT_BY_PATH

START WITH

CONNECT BY PRIOR.

In this case I would like to sorting to be based on the second SELECT statement in the UNION ALL.

So I used the second select statement within in parenthesis as shown below:

SELECT NULL alias1, NULL alias2 FROM table1, table2 WHERE col1.table1 = col2.table2 UNION ALL (SELECT col1, col2 FROM table1, table2 WHERE col1.table1 = col2.table2 START WITH <some col> CONNECT BY NOCYCLE PRIOR <some col> = <some col>) ORDER SIBLINGS BY col1, col2

I tried to use ORDER SIBLINGS BY but the error shows up that ORDER SIBLINGS BY is not allowed here

Please advice.