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 thoughts on “A little known ORDER BY extension

  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

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

  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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s