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: Select 1st or 3rd occurance?

Re: Select 1st or 3rd occurance?

From: Bob Maggio <rmaggio_at_courts.state.ny.usNOSPAM>
Date: Tue, 24 Jul 2001 18:13:58 GMT
Message-ID: <3B5DBB38.14DA81A0@courts.state.ny.usNOSPAM>

Thanks for all the suggestions! A great help. Hope to be able to return it someday.

Jim Harrington wrote:

> Bob,
>
> How about:
>
> select * from (
> select docket_id,
> legacy_docket_number,
> rownum count
> from docket where
> legacy_docket_number in(
> select legacy_docket_number
> from docket
> group by legacy_docket_number
> having count(legacy_docket_number ) >1)
> order by legacy_docket_number
> )
> where MOD(count,3 ) = 0;
>
> Thanks,
>
> Jim H.
>
> "Bob Maggio" <rmaggio_at_courts.state.ny.usNOSPAM> wrote in message
> news:3B56F7DF.3CD4840_at_courts.state.ny.usNOSPAM...
> > I have a select statement that selects items that are duplicate records,
> > and groups them in three's(3 occurances). I want to be able to select
> > only the first, or last occurance in that grouping. Is there a way to do
> > this, outside of cursoring thru? My select is as follows:
> >
> > select docket_id,
> > legacy_docket_number
> > from docket where
> > legacy_docket_number in(
> > select legacy_docket_number
> > from docket
> > group by legacy_docket_number
> > having count(legacy_docket_number ) >1)
> > order by legacy_docket_number
> > returns
> > 70220 B-00740-2001
> > 113947 B-00740-2001
> > 126424 B-00740-2001 (1st group)
> >
> > 70238 B-00741-2001
> > 126455 B-00741-2001
> > 113969 B-00741-2001 (2nd group)
> >
> > etc. I would like to be able to grab the 70220 and 70238 (1st in the
> > series). I can use a cursor, but would prefer a select for reporting
> > uses etc. Thanks
> > Bob Maggio
> >
> >
Received on Tue Jul 24 2001 - 13:13:58 CDT

Original text of this message

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