To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: Window function sort order help
Date: Wed, 14 Sep 2011 11:02:18 +0200
Lines: 32
Message-ID: <j4pqhm$tff$1@dough.gmane.org>
References: <25770AAABEA9A2499B5427E0DCEE9C9B323706179A@BE262.mail.lan>
	<CALNf6XOb=HhDwkvj1AbV4ajT73Vk2FF=6FRcuA1s=C8_=oMLWQ@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
In-Reply-To: 
 <CALNf6XOb=HhDwkvj1AbV4ajT73Vk2FF=6FRcuA1s=C8_=oMLWQ@mail.gmail.com>

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