Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
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.
“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
other useful features worth refering to are:
SYS_CONNECT_BY_PATH
CONNECT_BY_ROOT
CONNECT_BY_ISCYCLE
CONNECT_BY_ROOT
NOCYCLE
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