| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: "GROUP BY" - FAQ I've no doubt but I can't find the answer
The last part in the where clause you added in the subquery
comparing event time from main and subquery
(pev1.PEV_EVENT_DATETIME =
pev.PEV_EVENT_DATETIME)
just screws up everything and shows you didn't understand my answer.
The subquery needs to select the *last* applicable event.
By your addition it will select *all* applicable events, so it is not
surprising it doesn't work.
Please delete it.
Regards,
Sybrand Bakker, Senior Oracle DBA
"Richard Shea" <rshea_at_my-deja.com> wrote in message
news:43160f6f.0110091542.4501bf9b_at_posting.google.com...
> 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 - 22:59:29 CDT
 |
 |