From: | "Murray Long" <murray(at)skyrove(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Comparing sequential rows in a result |
Date: | 2008-10-29 09:25:23 |
Message-ID: | 56acee400810290225s36f4d3d1r7a3c079daa31ba7f@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Duffy | 2008-10-29 17:39:42 | simple SQL query |
Previous Message | Murray Long | 2008-10-29 09:01:39 | Comparing sequential rows in a result |