Re: SQL Help - Finding Next Lowest Value of Current Row Value

On Sat, Oct 1, 2011 at 5:30 PM, Jeff Adams <Jeff(dot)Adams(at)noaa(dot)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.