order by in a connect by

From: gaddoz <gaddoz_at_gmail.com>
Date: 30 Mar 2005 02:43:57 -0800
Message-ID: <37df65f6.0503300243.21bda6ed@posting.google.com>

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 Received on Wed Mar 30 2005 - 04:43:57 CST