Re: Select 1st or 3rd occurance?

Exactly how to do it, I can't say for sure but I would guess you can use the rank() function to give you the result you are looking for. Consult the documentation for some examples.

Sorry I couldn't be more specific.

Scott Watson.

"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 Thu Jul 19 2001 - 15:53:34 CDT