Re: Window function sort order help

Nicoletta Maia, 14.09.2011 10:30:
> 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.
>
That is not a valid PostgreSQL SQL statement.

Postgres does not use "backticks" for quoting, it uses the standard double quotes.

Thomas