Re: Comparing sequential rows in a result

Here's one solution:

create temp sequence tsec;
create temp table ttab as select nextval('tsec'), * from (select * from
events where event_type='a' order by timestamp desc) as troz;
select ttab.*, ttab2.timestamp-ttab.timestamp from ttab join ttab as ttab2
on ttab2.nextval = ttab.nextval+1;

This works, but seems a very messy way to accomplish somehting quite simple.

On Wed, Oct 29, 2008 at 11:01 AM, Murray Long <murray(at)skyrove(dot)com> wrote:

> I'm relatively new to SQL, and am frequently running into the same problem,
> How do I compare different rows in a result?
>
> for example:
> If I have a table of events consisting of a time stamp and the event type:
>
> timestamp, event_type
> 12:00 a
> 12:10 b
> 12:20 a
> ...
>
> I'd like to be able to select all the 'a' type events and calculate the
> time since the previous 'a' event, to get:
> timestamp, event_type, time_since_last
> 12:00 a 0:20
> 12:20 a NULL
>
> What's the best way to to accomplish this?
>
>
> Thanks in advance,
> Murray
>
>
>
>