Thank you. Interesting solution. Unfortunately, it's performance is not very good, since it involves joining a large
table3 times. I do have a solution that uses a temp table, but I was trying to rework it into a single query to
improveperformance.
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