RE: ORDER BY in CONNECT BY

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"

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) Received on Tue Feb 08 1994 - 23:29:25 CET