cool 9i feature - order SIBLINGS by ...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 10 Jul 2001 07:59:12 -0700
Message-ID: <9if58008jt@drn.newsguy.com>

Thought I might post some of the 9i features you probably won't read about in the press releases (as I find them)....

Order siblings by for CONNECT by statements -- you can order a connect by and preserve the hirearchy....

scott_at_TKYTE901.US.ORACLE.COM> 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
***TURNER
***WARD
**CLARK
***MILLER
**JONES
***FORD
****SMITH
***SCOTT
****ADAMS
14 rows selected.

scott_at_TKYTE901.US.ORACLE.COM> 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.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Tue Jul 10 2001 - 09:59:12 CDT