Re: Aggregate query problem

They are supported in 9i.

Richard

Tom Barnes wrote:
>
> Richard Kuhler <noone_at_nowhere.com> wrote in message news:<AjnX8.89950$xy.30584456_at_twister.socal.rr.com>...
> > Here's one alternative...
> >
> > select distinct empid, first_value(eventid) over (
> > partition by empid
> > order by abs(to_date(:d)-datetime)) as closest_eventid
> > from events
> > where datetime between to_date(:sd) and to_date(:ed)
> >
> > However, this query only returns one event per employee.
>
> This is actually exactly what I want. If an employee has two events
> with the same datetime my query returns both rows, which I don't want.
> Your query is also much faster (consistent gets went down 50%).
>
> The only problem is that analytical functions don't seem to be
> supported in PL/SQL for my version of Oracle (8.1.6.1). The solution
> seems to be to write my queries in PL/SQL using dynamical SQL. Does
> anyone know in which version analytical functions are supported in
> PL/SQL?
>
> Thanks alot,
>
> Tom
Received on Fri Jul 12 2002 - 14:02:31 CDT