Re: [SQL] Window function sort order help

[Thomas Kellerer]

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



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql