Complicated query

Currently I have an INSERT...SELECT statement like this:

BEGIN
INSERT /*+ APPEND */ INTO t_1 (
  SELECT
    id_1, id_2, id_3, strt,
    AVG(speed), MIN(speed), MAX(speed), NULL   FROM t_2 WHERE timestamp BETWEEN strt AND stop   GROUP BY id_1, id_2, id_3
);
END; this works fine, but the NULL above should be the timestamp from t_2 where t_2.speed = MAX(speed) for the GROUP BY. This seems a little convoluted, but I simply need the time of the peak speed in t_2 for id_1,id_2,id_3. I have a statement like

UPDATE t_1 a SET a.hi_ts = (
  SELECT MIN(ts) FROM t_2 b WHERE

    a.id_1 = b.id_1 AND
    a.id_2 = b.id_2 AND
    a.id_3 = b.id_3 AND
    b.timestamp BETWEEN strt AND stop);

Needless to say, this is ridiculously inefficient!

Is there any other way to go about this? I tried to use some sort of join in the INSERT..SELECT statement, but you cannot reference aggregates (ie MAX(x)) in the WHERE clause...

I had considered defining a function to determine the peak time given the id's and the peak value and using this in the INSERT..SELECT statement directly. Would this be more efficient?

Please email any reply to bkirkbride_at_cw.net and thanks in advance...

Brian Kirkbride
Cable & Wireless, USA

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 02 1999 - 13:00:45 CST