Re: "GROUP BY" - FAQ I've no doubt but I can't find the answer

postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0110090402.5cb52372_at_posting.google.com>...
> rshea_at_my-deja.com (Richard Shea) wrote in message news:<43160f6f.0110082246.63e4d0e4_at_posting.google.com>...
[original question snipped]

Hi Sybrand - Thanks for your suggestion. Unfortunately it's not working for me. I still get all the events rather than just the most recent one...

Just to be sure that I haven't made a mistake here is your suggestion ...

>
> This is a generic problem with a generic solution
> : use a correlated subquery to retrieve the highest/latest record applicable
>
> like this
>
>
> select
> PAR_AUTOID,
> (to_char(PEV_EVENT_DATETIME,'YYYYMMDD HH:MI:SS'))
> from
> COP_PARTICIPANT P
> COP_PARTICIPANT_EVENT PEV
> where
> (
> (P.PAR_SAM_ID=200000)
> AND
> (P.PAR_AUTOID = PEV.PEV_PAR_ID)
> )
> and pev_event_date_time
> = (select max(pev_event_date_time)
> from cop_participant_event pev1
> where pev1.<primary key> = pev.<primary key>)
>
>

... here is how I have recast it (only very slight changes)...

select
PAR_AUTOID,
(to_char(PEV_EVENT_DATETIME,'YYYYMMDD HH:MI:SS')) from
COP_PARTICIPANT P,
COP_PARTICIPANT_EVENT PEV
where
(

   (P.PAR_SAM_ID=200000)
   AND
   (P.PAR_AUTOID = PEV.PEV_PAR_ID)
 )
and
pev_event_datetime = (select max(pev_event_datetime) from cop_participant_event pev1

                       where 
                      (pev1.PEV_PAR_ID = pev.PEV_PAR_ID)
                      AND
                      (pev1.PEV_PET_EVENT_TYPE =
pev.PEV_PET_EVENT_TYPE)
                      AND
                      (pev1.PEV_EVENT_DATETIME =
pev.PEV_EVENT_DATETIME)
                       );

... now when I run this I get _all_ the COP_PARTICIPANT_EVENT rows for each COP_PARTICIPANT row rather than just that _one_ which is the most recent (as measured by its DATETIME value).

Have I done something wrong ? Maybe I didn't make the requirement clear originally ? Any other/further suggestions are welcome.

regards

richard shea
rshea_at_my-deja.com

> Hth,
>
> Sybrand Bakker
> Senior Oracle DBA
Received on Tue Oct 09 2001 - 18:42:19 CDT