Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Ordering Hierarchical Queries
Junmin Lin wrote:
>
> Hi,
>
> I have a question about hierarchical queries.
>
> Suppose I have a table:
>
> PARENT NOT NULL VARCHAR2(10)
> CHILD NOT NULL VARCHAR2(10)
> SORT_ORDER NUMBER(5)
>
> I want to do a hierachical query, I could do:
>
> select parent, child, sort_order, level
> from relation
> connect by parent = prior child start with parent = 'A' ;
>
> But, if I want the out put sorted via sort_order and at the same time
> preseved the level.
> Lin Junmin.
I used a temporary table to store the result of the query to conserve both the level and the order
delete from tree;
commit;
insert into tree (seqnr,levl,child,parent)
select rownum,level,child,upper(kode)
from your_table
start with parent=upper(kode)
connect by prior child=parent;
commit;
kode is the parameter (parent) to start the procedure wirth
-- ****************************************************************** ****************** A L C A T E L T E L E C O M ****************** Herman Viaene Berkenrodelei 33, B-2660 Hoboken, Belgium Division: Radio Space and Defence Tel: (32/3)8295591 Fax: (32/3)8295502 OIS-mail: viaeneh_at_AM@BTMA97 Internet-mail: viaeneh_at_btmaa.bel.alcatel.be X400: Viaene Herman (C=BE,A=RTT,P=ALCANET,OU1=BELA1,O=ALCATEL,S="Viaene",G="Herman") ******************************************************************Received on Thu Mar 26 1998 - 00:00:00 CST