A little known ORDER BY extension

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

1. “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

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

   Reply

3. 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.