Re: Aggregate query problem

From: Tom Barnes <barnest_at_san.rr.com>
Date: 12 Jul 2002 11:21:00 -0700
Message-ID: <ae6b6116.0207121020.1ed4d109@posting.google.com>

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 - 13:21:00 CDT