Learning is not a spectator sport

A little known ORDER BY extension

1–2 minutes

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 responses to “A little known ORDER BY extension”

  1. stewashton Avatar
    stewashton

    “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. krishna Avatar
    krishna

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

    Reply
  3. krishna Avatar
    krishna

    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

Leave a reply to krishna Cancel reply

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

Trending