Thanks for the suggestions Chris (and Chris). After a bit more investigation I stumbled upon the Window functions. The approach below turned out to be much more efficient that a function or self join approach. The SQL that I used is provided below (event_id and mmsi uniquely identify a vessel transit for which I wished to compute how much time had elapsed between successive records): SELECT a.event_id, a.mmsi, (a.epoch - lag(epoch) OVER (PARTITION BY event_id, mmsi ORDER BY epoch ASC))/60 AS elapsed FROM dmas_ais a Jeff From: ccurvey@xxxxxxxxx [mailto:ccurvey@xxxxxxxxx] On Behalf Of Chris Curvey On Sat, Oct 1, 2011 at 5:30 PM, Jeff Adams <Jeff.Adams@xxxxxxxx> wrote: Greetings, Would a self-join with a MAX() help, like this? (Where "v" is your vessel_id and "e" is your time value?) create table stuff ( v int , e timestamp ); insert into stuff (v, e) values (1, '1/1/2011'); insert into stuff (v, e) values (1, '1/2/2011'); insert into stuff (v, e) values (1, '1/3/2011'); insert into stuff (v, e) values (2, '2/1/2011'); insert into stuff (v, e) values (2, '2/2/2011'); select a.v, a.e, max(b.e), a.e - max(b.e) from stuff a join stuff b on a.v = b.v where a.e > b.e group by a.v, a.e I don't have a multi-million row table handy, but I'd be interested to hear your results. -- e-Mail is the equivalent of a postcard written in pencil. This message may not have been sent by me, or intended for you. It may have been read or even modified while in transit. e-Mail disclaimers have the same force in law as a note passed in study hall. If your corporate attorney says that you need an disclaimer in your signature, you need a new corporate attorney. |