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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
