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@skyrove.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
