Re: Aggregate query problem

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. Your query returns all ties.

Richard

Tom Barnes wrote:
>
> I have a problem that I have simplified into the following scenario. I
> have a table Events, that stores events for employees:
>
> CREATE TABLE Events(EventID NUMBER, EmpID NUMBER, DateTime DATE);
> CREATE INDEX Events_IX1 ON Events(EmpID, DateTime);
>
> I want a fast query that, for every EmpID, returns the closest event
> to a specified date, constrained by a specified date/time window.
>
> My best attempt so far is a query like this:
>
> SELECT EventID
> FROM Events,
> ( SELECT MIN(ABS(:1-DateTime)) MinAbs, EmpID
> FROM Events
> WHERE DateTime BETWEEN :2 AND :3
> GROUP BY EmpID ) Minimums
> WHERE ABS(:1-DateTime) = Minimums.MinAbs
> AND Minimums.EmpID = Events.EmpID;
>
> which gives me the following execution plan:
>
> --------------------------------------------
> |Operation | Name |
> --------------------------------------------
> |SELECT STATEMENT | |
> | NESTED LOOPS | |
> | VIEW | |
> | SORT GROUP BY | |
> | INDEX FULL SCAN |EVENTS_IX1 |
> | TABLE ACCESS BY INDEX ROWID |EVENTS |
> | INDEX RANGE SCAN |EVENTS_IX1 |
> --------------------------------------------
>
> (The FULL SCAN of EVENTS_IX1 is replaced by a RANGE SCAN on my real
> system). As you can see EVENTS_IX1 is scanned twice. The inline view
> is picking out the minimums, why can't it also return the
> corresponding EventIDs so I wouldn't need a separate join (and hence
> an extra index scan) to get the EventIDs?
>
> Can I use analytical functions for this?
>
> Oracle 8.1.6 Enterprise Edition on Solaris 2.8
>
> Thanks a lot,
>
> Tom
Received on Thu Jul 11 2002 - 17:06:24 CDT