pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: Window function sort order help
Date: Wed, 14 Sep 2011 11:02:18 +0200
Message-ID: <j4pqhm$tff$1@dough.gmane.org> (raw)
In-Reply-To: <CALNf6XOb=HhDwkvj1AbV4ajT73Vk2FF=6FRcuA1s=C8_=oMLWQ@mail.gmail.com>
References: <25770AAABEA9A2499B5427E0DCEE9C9B323706179A@BE262.mail.lan>
	<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

view thread (8+ messages)  latest in thread

Message-ID: <j4pqhm$tff$1@dough.gmane.org>
Permalink:  ../j4pqhm$tff$1@dough.gmane.org/
Also on:    postgresql.org/message-id/j4pqhm$tff$1@dough.gmane.org

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: spam_eater@gmx.net
  Subject: Re: Window function sort order help
  In-Reply-To: <j4pqhm$tff$1@dough.gmane.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox