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:
1 2 3 4 5 6 7 8 9 10 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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. |
Sanjay
Ajesh Rajan
Gokhan Atil
SIDDHARTHA PENCHALA
Vamsi