On Sat, Oct 1, 2011 at 5:30 PM, Jeff Adams <jeff.ad...@noaa.gov> wrote:
> Greetings, > > I have a large table (~19 million records). Records contains a field > identifying a vessel and a field containing an time (epoch). Using the > current rows vessel and time values, I need to be able to find the next > lowest time value for the vessel and use it to compute how much time has > elapsed between the records. I have tried a scalar subquery in the SELECT, > which works, but it runs quite slowly. Is there an alternative approach > that > might perform better for this type of query. Any information would be > greatly appreciated. Thanks... > > Jeff > > > 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.
