Re: Comparing sequential rows in a result
| От | Murray Long |
|---|---|
| Тема | Re: Comparing sequential rows in a result |
| Дата | |
| Msg-id | 56acee400810290225s36f4d3d1r7a3c079daa31ba7f@mail.gmail.com обсуждение исходный текст |
| Ответ на | Comparing sequential rows in a result ("Murray Long" <murray@skyrove.com>) |
| Список | 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.
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
В списке pgsql-sql по дате отправления: