Re: order by in a connect by

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"

------------------------------------------------------------