2011/9/13 Dianna Harter <dharter@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