Re: Window function sort order help

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.

Sorry, I responded with the wrong email address..

Nicoletta