RE: ORDER BY in CONNECT BY

In article <CKxFt2.7Br_at_cix.compulink.co.uk>, asc_at_cix.compulink.co.uk ("Tony Scott") writes:
|>I have battled with this problem for a thesaurus and my conclusion is
|>that you can't do it in SQL*Plus. I am about to do it using
|>SQL*Reportwriter. The Oracle Response centre faxed me some horrendous
|>looking solution that they said they didn't understand themselves. It
|>involves using PL/SQL and a table to simulate a stack. The fax ends with
|>the words "I have not tested this answer: I am assuming it is correct"
|>

IT'S POSSIBLE ! see my transaction earlier in the news !
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

|>moebius_at_athene.informatik.uni-bonn.de (Dirk Moebius) wrote:
|>> 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
|>
|>Tony Scott
|>(via OLR)
|>
 

-- 
______________________________________________________________________________

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 
_/_/_/_/ .  _/_/_/_/ .   _/_/ .   _/_/_/_/ .                       ** 
______________________________________________________________________________