Home -> Usenet -> comp.databases.oracle -> ORDER BY in CONNECT BY

ORDER BY in CONNECT BY

From: Dirk Moebius <moebius_at_athene.informatik.uni-bonn.de>
Date: 8 Feb 1994 13:52:14 GMT
Message-ID: <2j85ee$c6c_at_news.rhrz.uni-bonn.de> 






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
Received on Tue Feb 08 1994 - 14:52:14 CET












Original text of this message