Re: sql help, please

  • From: Michael Moore <michaeljmoore@xxxxxxxxx>
  • To: Eugene Pipko <eugene.pipko@xxxxxxxxxxxx>
  • Date: Tue, 26 Jan 2010 15:21:36 -0800

Maybe this will work for you then:

WITH mytab AS
     ( SELECT    animal, LTRIM( SYS_CONNECT_BY_PATH( grp, ',' ), ',' ) csv
            FROM ( SELECT animal,
                          ROW_NUMBER( ) OVER( PARTITION BY animal ORDER BY 1
) rnum,
                          grp
                    FROM c )
      CONNECT BY animal = PRIOR animal AND   rnum
                                           - 1 = PRIOR rnum
      START WITH rnum = 1 )
SELECT *
  FROM mytab myt
 WHERE NOT EXISTS( SELECT 1
                    FROM mytab mt
                   WHERE mt.animal = myt.animal AND LENGTH( mt.csv ) >
LENGTH( myt.csv ));

On Tue, Jan 26, 2010 at 2:59 PM, Eugene Pipko <eugene.pipko@xxxxxxxxxxxx>wrote:

>  Thank you Michael, but I am on 9.2
>
>
>
> ORA-00904: "CONNECT_BY_ISLEAF": invalid identifier
>
>
>
> Eugene Pipko
>
> Seattle Pacific Industries
>
> office: 253.872.5243
>
> cell: 206.304.7726
>
> P  Please consider the environment before printing this e-mail
>
>
>
> *From:* Michael Moore [mailto:michaeljmoore@xxxxxxxxx]
> *Sent:* Tuesday, January 26, 2010 2:55 PM
>
> *To:* Eugene Pipko
> *Cc:* oracle-l@xxxxxxxxxxxxx
> *Subject:* Re: sql help, please
>
>
>
> Looks like you already got your answer but ...
>
> CREATE TABLE C
>
> (
>
>   ANIMAL  VARCHAR2(5 BYTE),
>
>   GRP     NUMBER(6)
>
> );
>
>
>
> Insert into C   (ANIMAL, GRP) Values   ('cat', 1);
>
> Insert into C   (ANIMAL, GRP) Values   ('frog', 1);
>
> Insert into C   (ANIMAL, GRP) Values   ('frog', 66);
>
> Insert into C   (ANIMAL, GRP) Values   ('cat', 33);
>
> Insert into C   (ANIMAL, GRP) Values   ('cat', 44);
>
> Insert into C   (ANIMAL, GRP) Values   ('cat', 66);
>
> Insert into C   (ANIMAL, GRP) Values   ('frog', 44);
>
> Insert into C   (ANIMAL, GRP) Values   ('toad', 44);
>
> COMMIT;
>
>
>
> SELECT     animal, LTRIM( SYS_CONNECT_BY_PATH( grp, ',' ), ',' )
>
>       FROM ( SELECT animal, ROW_NUMBER( ) OVER( PARTITION BY animal ORDER
> BY 1 ) rnum,  grp
>
>               FROM c )
>
>      WHERE CONNECT_BY_ISLEAF = 1
>
> CONNECT BY animal = PRIOR animal AND   rnum - 1 = PRIOR rnum
>
> START WITH rnum = 1;
>

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2010