2011/9/13 Dianna Harter <dhar...@mynewplace.com>: Hi,
> [snip] > Any suggestions to get the order by to occur first then the partition by or > maybe there another approach that I could use? 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 To avoid the LEFT JOIN, you can move the control in the sub-query: 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` WHERE NOT EXISTS ( SELECT * FROM `Table` AS `Z` WHERE `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` ) GROUP BY `X`.`consumer_id`, `X`.`move_date` , `X`.`history_timestamp` ORDER BY `X`.`consumer_id`, `X`.`history_timestamp` ASC With Y I select history_timestamp preceding the current row with the same move_date. With Z I verify that no changes have occurred to move_date between X.history_timestamp and Y.history_timestamp. Ciao! Nicoletta -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql