Thank you. Interesting solution. Unfortunately, it's performance is not very good, since it involves joining a large table 3 times. I do have a solution that uses a temp table, but I was trying to rework it into a single query to improve performance.
Thank you again for your help. Dianna 2011/9/13 nicoletta maia : [snip] > I tried to write the query without using the window: > > SELECT `X`.`consumer_id`, `X`.`move_date` , `X`.`history_timestamp` , > MIN( `Y`.`history_timestamp` ) AS `start_time` > FROM > `Table` AS `X` > JOIN > `Table` AS `Y` > ON `X`.`consumer_id` = `Y`.`consumer_id` > AND `X`.`move_date` = `Y`.`move_date` > AND `X`.`history_timestamp` >= `Y`.`history_timestamp` > LEFT JOIN > `Table` AS `Z` > ON `X`.`consumer_id` = `Z`.`consumer_id` > AND `X`.`move_date` <> `Z`.`move_date` > AND `X`.`history_timestamp` >= `Z`.`history_timestamp` > AND `Y`.`history_timestamp` <= `Z`.`history_timestamp` > WHERE `Z`.`consumer_id` IS NULL > GROUP BY `X`.`consumer_id`, `X`.`move_date` , `X`.`history_timestamp` > ORDER BY `X`.`consumer_id`, `X`.`history_timestamp` ASC -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
