From: | sub3 <steve(at)subwest(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Query to match location transitions |
Date: | 2008-11-14 19:12:33 |
Message-ID: | 20506709.post@talk.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
I was hoping someone could help me build a better query. I have a table of
time/locations. Occasionally, we have multiple timestamps for the same
location. I would like to remove those extra timestamps and only show the
transition from one location to another. So...
create table time_locations (
id integer,
timestamp double precision,
location integer
)
Data:
1,1197605841,1
2,1197608001,2
3,1197609802,2
4,1197611951,2
5,1199145360,2
6,1199145480,3
7,1199147280,3
8,1199149140,3
9,1199151300,1
10,1199152000,3
I would like to return a table like:
1197605841,1,1197608001,2
1199145360,2,1199145480,3
1199149140,3,1199151300,1
1199151300,1,1199152000,3
The only way I can think of to do this would be a procedure which would do a
large loop over the
table (sorted by time) returning a row when last.location <> this.location.
However, when I try this on a
large table, it seems like the 'select into' doesn't order & ruins the whole
solution.
Is there a query approach?
Thanks.
--
View this message in context: http://www.nabble.com/Query-to-match-location-transitions-tp20506709p20506709.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Raj Mathur | 2008-11-15 04:06:34 | Re: Enc: Help to replace caracter |
Previous Message | Marco Antonio | 2008-11-14 16:55:25 | Re: Enc: Help to replace caracter |