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

rshea_at_my-deja.com (Richard Shea) wrote in message news:<43160f6f.0110082246.63e4d0e4_at_posting.google.com>...
> Hi - I've seen very similar questions to this in the archives but not
> quite this one, can someone help me out ?
>
> I've got two tables, a 'Participants' table and a 'Participants Event'
> table. 'Participants Event' is a child of 'Participants' and gets a
> timestamped record inserted when a 'Participant' does something.
>
> Now if I want to see the most recent event alongside a given
> Participant I can do something like this ...
>
> select
> PAR_AUTOID,
> MAX(to_char(PEV_EVENT_DATETIME,'YYYYMMDD HH:MI:SS'))
> from
> COP_PARTICIPANT,
> COP_PARTICIPANT_EVENT
> where
> (
> (PAR_SAM_ID=200000)
> AND
> (PAR_AUTOID = PEV_PAR_ID)
> )
> GROUP BY PAR_AUTOID;
>
> ... and get this ...
>
> PAR_AUTOID MAX(TO_CHAR(PEV_E
> ---------- -----------------
> 3899 20000805 01:30:00
>
> ... the trouble is I need to know not only the date time of the event
> (from the Participants Event table )but also the 'event
> type'(PEV_PET_EVENT_TYPE from the Participants Event table). So my
> first thought was to do something like this ...
>
> select
> PAR_AUTOID,
> PEV_PET_EVENT_TYPE,
> MAX(to_char(PEV_EVENT_DATETIME,'YYYYMMDD HH:MI:SS'))
> from
> COP_PARTICIPANT,
> COP_PARTICIPANT_EVENT
> where
> (
> (PAR_SAM_ID=200000)
> AND
> (PAR_AUTOID = PEV_PAR_ID)
> )
> GROUP BY PAR_AUTOID,PEV_PET_EVENT_TYPE;
>
> ... but then I end up with all the COP_PARTICIPANT_EVENT records and
> not just the one with the latest datetime on it.
>
> My understanding is that if something in the select list is not an
> aggregrate function it has to be in the group by and in many cases I
> can see why but in this case there would be no ambiguity in the
> output, I'm just wanting to know more about the record which is the
> MAX.
>
> I'm certain there must be a way to do this - can anyone point me in
> the right direction please ?
>
> thanks
>
> richard shea
> rshea_at_my-deja.com

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>)

Hth,

Sybrand Bakker
Senior Oracle DBA Received on Tue Oct 09 2001 - 07:02:36 CDT