A little known ORDER BY extension

Posted by

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

  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

Got some thoughts? Leave a comment

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

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