Re: Window function sort order help

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