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

Richard Shea wrote:
>
> 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

hi richard,

i know, that this is not the way you originally wanted to do this by try this one (hope it will work)
SELECT whateveryouwant
from whereveryouwant
where ( pev_event_datetime = select max...);

yours
daniel Received on Tue Oct 09 2001 - 04:49:08 CDT