Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: order by in a connect by

Re: order by in a connect by

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 30 Mar 2005 21:05:42 +0800
Message-ID: <424AA426.17D4@yahoo.com>


gaddoz wrote:
>
> Hi,
> i'm trying to use the connect by clause,
> so I have created this test table:
>
> CREATE TABLE "TRY"."GEN" (
> "GEN_COD" NUMBER(10) NOT NULL,
> "GEN_DES" VARCHAR2(50) NOT NULL,
> "GEN_PAR" NUMBER(10) NULL,
> CONSTRAINT "PK_GEN_COD" PRIMARY KEY("GEN_COD")
> )
>
> and inserted some test values:
> INSERT INTO "TRY"."GEN"("GEN_COD" ,"GEN_DES" ,"GEN_PAR")VALUES(1,'1'
> ,0)
> INSERT INTO "TRY"."GEN"("GEN_COD" ,"GEN_DES" ,"GEN_PAR")VALUES(2,'1.1'
> ,1)
> INSERT INTO "TRY"."GEN"("GEN_COD" ,"GEN_DES" ,"GEN_PAR")VALUES(3,'1.2'
> ,1)
> INSERT INTO "TRY"."GEN"("GEN_COD" ,"GEN_DES" ,"GEN_PAR")VALUES(4,'1.3'
> ,1)
> INSERT INTO "TRY"."GEN"("GEN_COD" ,"GEN_DES"
> ,"GEN_PAR")VALUES(5,'1.1.1' ,2)
> INSERT INTO "TRY"."GEN"("GEN_COD" ,"GEN_DES"
> ,"GEN_PAR")VALUES(6,'1.1.2' ,2)
> INSERT INTO "TRY"."GEN"("GEN_COD" ,"GEN_DES" ,"GEN_PAR")VALUES(7,'2'
> ,0)
>
> Now i'm trying to get all the values, with this, and it's working
> good.
> SELECT LPAD(' ',6*(level-1)) || GEN_DES AS MYGENDES, level AS GEN_LEV
> FROM GEN
> STRAT WITH GEN_PAR = 0
> CONNECT BY PRIOR GEN_COD = GEN_PAR
>
> But now, I would like to order all DESC, starting with the gen_des =
> 2, then with gen_des = 1, but remaning the nested values under the 1
> and order DESC.
>
> It's possible to do?
> How can I do it?
>
> Thanks you all.
>
> Gaddoz

not exactly sure what you're asking, but check out "order siblings"

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------
Received on Wed Mar 30 2005 - 07:05:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US