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

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 Received on Tue Oct 09 2001 - 01:46:32 CDT