[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] Comparing sequential rows in a result
From:       "Murray Long" <murray () skyrove ! com>
Date:       2008-10-29 9:25:23
Message-ID: 56acee400810290225s36f4d3d1r7a3c079daa31ba7f () mail ! gmail ! com
[Download RAW message or body]

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
>
>
>
>

[Attachment #3 (text/html)]

Here&#39;s one solution:<br><br>create temp sequence tsec;<br>create temp table ttab \
as select nextval(&#39;tsec&#39;), * from (select * from events where \
event_type=&#39;a&#39; order by timestamp desc) as troz;<br>select ttab.*, \
ttab2.timestamp-ttab.timestamp from ttab join ttab as ttab2 on ttab2.nextval = \
ttab.nextval+1;<br> <br>This works, but seems a very messy way to accomplish \
somehting quite simple.<br><br><br><div class="gmail_quote">On Wed, Oct 29, 2008 at \
11:01 AM, Murray Long <span dir="ltr">&lt;<a \
href="mailto:murray@skyrove.com">murray@skyrove.com</a>&gt;</span> wrote:<br> \
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); \
margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">I&#39;m relatively new to SQL, and am \
frequently running into the same problem, How do I compare different rows in a \
result?<br> <br>for example:<br>If I have a table of&nbsp; events consisting of a \
time stamp and the event type:<br> <br>timestamp,&nbsp;&nbsp;&nbsp;&nbsp; \
event_type<br>12:00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
a<br>12:10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
b<br>12:20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
a<br>...<br><br>I&#39;d like to be able to select all the &#39;a&#39; type events and \
calculate the time since the previous &#39;a&#39; event, to get:<br>

timestamp,&nbsp;&nbsp;&nbsp;&nbsp; event_type,&nbsp;&nbsp;&nbsp;&nbsp; \
time_since_last<br> 12:00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& \
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
0:20<br> 12:20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& \
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \
NULL<br><br>What&#39;s the best way to to accomplish this? <br><br><br>Thanks in \
advance,<br><font color="#888888">Murray<br><br><br><br> \
</font></blockquote></div><br>



[prev in list] [next in list] [prev in thread] [next in thread]