Re: ORDER BY in CONNECT BY

In article <2j85ee$c6c_at_news.rhrz.uni-bonn.de>, moebius_at_athene.informatik.uni-bonn.de (Dirk Moebius) writes:
|>Hello!
|>I have a question concerning PL/SQL.
|>How do I sort the hierarchical output of records, that have been fetched
|>by a CONNECT BY - Statement?
|>
|>Suppose, I have the following
|>table EMP: dept number(3);
|> name char(30);
|> job char(20);
|>
|>with the data:
|>10, "Smith", "MANAGER", 20, "Brian", "MANAGER",
|>10, "Jones", "CLERK", 20, "Clark", "CLERK",
|>10, "Wilson", "CLERK", 20, "Adams", "CLERK",
|>10, "Miller", "CLERK", 20, "Willis", "CLERK".
|>
|>The following SELECT-statement:
|>
|>SELECT dept,lpad(name,' ',(LEVEL-1)*2), job
|> FROM emp
|> START WITH job='MANAGER'
|> CONNECT BY dept;
|>
|>brings up:
|>dept name job
|>---- ------------ -------------
|>10 Smith MANAGER
|>10 Jones CLERK
|>10 Wilson CLERK
|>10 Miller CLERK
|>20 Brian MANAGER
|>20 Clark CLERK
|>20 Adams CLERK
|>20 Willis CLERK
|>
|>The column name is not sorted. When I use an ORDER BY name statement,
|>the hierarchical order will be overwritten.
|>How can I sort every lever seperately? Like this:
|>20 Brian
|>20 Adams
|>20 Clark
|>20 Willis
|>10 Smith
|>10 Jones
|>10 Miller
|>10 Wilson
|>
|>Thanks in advance,
|> Dirk.
|>--
|> Dirk Moebius | RuGu CONSULTING
|>Internet: moebius_at_athene.informatik.uni-bonn.de | Oracle - Software -
|>Snail: Pariser Str. 54, Zi. 319, | Development
|> 53117 Bonn, Germany |
|>Phone: +49 228 687789
|>
Well, this question is very interesting and has been developped in the 'Bulletin Technique No 9 - ORACLE FRANCE' in an article titled 'Connect by ... prior : hierarchies under SQL'.
The ORDER BY clause is preponderant on CONNECT BY ... PRIOR clauses and cancel this last one.
To select rows with the same level according to a sort criteria, it's necessary and it's enough to indicate to ORACLE to use an access way corresponding to this criteria.
In your exemple, you must :

• create an index on the name
• force the ORACLE optimizer to use this index like access way. i.e. SQL> create index emp.name on emp(name); .. SQL> select lpad ........ from emp connect by prior ... and name > 'A' ^^^^^^^^^^^^^^ start with ... ; --

  ---

Yves NOEL   -   Database Administrator         
C.I.T.I. (batiment M4)                             Mail  : noel_at_univ-lille1.fr
Universite des Sciences & Technologies de Lille    Phone :    (33) 20.43.42.70
59655 Villeneuve d'Ascq Cedex - FRANCE             Fax   :    (33) 20.43.66.25 
______________________________________________________________________________

   _/_/_/_/    _/_/_/_/   _/_/_/_/   _/_/_/_/             I use ORACLE v6.0.36
  _/_/          _/_/       _/_/       _/_/                         on
 _/_/          _/_/       _/_/       _/_/                 DEC RISC ULTRIX v4.3 
_/_/_/_/ .  _/_/_/_/ .   _/_/ .   _/_/_/_/ .                       ** 
______________________________________________________________________________