HI Raj If source changes, in this case from 1 to 2, then etime would be the last value of stime for source =1; So for source 1 it starts at stime 13:00 and continues till 13:02 (etime).
This should result in 3 records, because source is 1, then 2, then 1 again. I hope this explains ? -----Original Message----- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Raj Mathur (??? ?????) Sent: 24 May 2012 01:59 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Flatten table using timestamp and source On Thursday 24 May 2012, Elrich Marx wrote: > I am quite new to Postgres, so please bear with me. > > I have a table with data in the following format: > > Table name : Time_Source_Table > > Source , Stime > 1, "2012-05-24 13:00:00" > 1, "2012-05-24 13:01:00" > 1, "2012-05-24 13:02:00" > 2, "2012-05-24 13:03:00" > 2, "2012-05-24 13:04:00" > 1, "2012-05-24 13:05:00" > 1, "2012-05-24 13:06:00" > > I’m trying to get to a result that flattens the results based on > source, to look like this: > > Source, Stime, Etime > 1, "2012-05-24 13:00:00","2012-05-24 13:02:00" > 2, "2012-05-24 13:03:00","2012-05-24 13:04:00" > 1, "2012-05-24 13:05:00","2012-05-24 13:06:00" > > Where Etime is the last Stime for the same source. How do you figure out that the Etime for (1, 13:00:00) is (1, 13:02:00) and not (1, 13:01:00)? Regards, -- Raj -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
