A little known ORDER BY extension

Within a CONNECT BY statement, you can order siblings, that is, under a particular branch of the hierarchy, the child entries can be ordered, but you still preserve the hierarchy.


SQL> select lpad('*', level, '*' ) || ename ename
  2  from emp
  3  start with mgr is null
  4  connect by prior empno = mgr
  5  order SIBLINGS by ename
  6  /

ENAME
------------------------------
*KING
**BLAKE
***ALLEN      <==| 
***JAMES      <==|
***MARTIN     <==| ordered within the "BLAKE" branch
***TURNER     <==|
***WARD       <==|
**CLARK
***MILLER
**JONES
***FORD
****SMITH
***SCOTT
****ADAMS

14 rows selected.

SQL> select lpad('*', level, '*' ) || ename ename
  2  from emp
  3  start with mgr is null
  4  connect by prior empno = mgr
  5  order SIBLINGS by ename DESC
  6  /

ENAME
------------------------------
*KING
**JONES
***SCOTT
****ADAMS
***FORD
****SMITH
**CLARK
***MILLER
**BLAKE
***WARD
***TURNER
***MARTIN
***JAMES
***ALLEN

14 rows selected.

3 comments

stewashton says:

January 22, 2016 at 5:08 pm

“the child entries can be ordered” and if there are multiple roots, the root entries are ordered too. You probably meant that…

select lpad(‘*’, level, ‘*’ ) || ename ename
from emp
start with ename in (‘BLAKE’, ‘CLARK’, ‘JONES’)
connect by prior empno = mgr and level <= 2
order SIBLINGS by emp.ename DESC;

*JONES
**SCOTT
**FORD
*CLARK
**MILLER
*BLAKE
**WARD
**TURNER
**MARTIN
**JAMES
**ALLEN

Reply
krishna says:

October 9, 2016 at 5:43 pm

other useful features worth refering to are:
SYS_CONNECT_BY_PATH
CONNECT_BY_ROOT
CONNECT_BY_ISCYCLE
CONNECT_BY_ROOT
NOCYCLE

Reply
krishna says:

October 9, 2016 at 9:38 pm

SYS_CONNECT_BY_PATH

takes two arguments. column name and a character string. Retutns a list containing each value of the column from the root node to the current node.

SELECT SYS_CON/NECT_BY_PATH(LANME, ‘/’)….
/KING
/KING/JONES
/KING/JONES/SCOTT

CYCLES

Cycles are not allowed in hierarchical data. sometimes you find them. say mananger_emp_id column of CEO (top most) has mistakenly been given the id of an employee.
this will give error: 01436 connect by loop in user data.
to avoid this error add NOCYCLE as follows:

…..
CONNECT BY NOCYCLE PRIOR EMP_ID = MANAGER_EMP_ID

CONNECT_BY_ISCYCLE

to identify cycles in hierarchical data use the psuedo cloumn CONNECT_BY_ISCYCLE which return 1 if the current row has a child that is also its ancestor , otherwise it returns 0.

select lname, CONNECT_BY_ISCYCLE
……

KING 0
MARTIN 1
…..

CONNECT_BY_ISLEAF

to filter only leaf nodes, use CONNECT_BY_ISLEAF

SELECT…
FROM….
WHERE CONNECT_BY_ISLEAF = 1
START WITH…
CONNECT BY…

CONNECT_BY_ROOT

Used to retrieve a value from a node’s root.

SELECT lname, CONNECT_BY_ROOT lname “top manager”
…….

JONES JONESRe
SCOTT JONES
BLAKE BLAKE
ALLEN BLAKE

IE., Returns the root row for each row

Reply