Mail-Followup-To: pgsql-sql@postgresql.org
To: pgsql-sql@postgresql.org
From: Tim Landscheidt <tim@tim-landscheidt.de>
Subject: Re: Lag and lead window functions order by weirdness
Date: Sun, 18 Oct 2009 16:51:38 +0000
Organization: <URI:http://www.tim-landscheidt.de/>
Lines: 20
Message-ID: <m31vl0r6g5.fsf@passepartout.tim-landscheidt.de>
References: <bddc86150910091726s704277f9u7ddcead9874889b3@mail.gmail.com>
	<bddc86150910120009p3ae9d578x2dac139a71e4d045@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Mail-Copies-To: never
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:N9ZOxt3BKg/0eqC3jGY+8hSL/ys=

Thom Brown <thombrown@gmail.com> wrote:

> [...]
> Having a look around, it looks as if Postgres might be misbehaving.
>  According to this page,
> http://my.safaribooksonline.com/0596004818/sqlnut2-CHP-4-SECT-3, the ORDER
> BY in the window function's clause shouldn't be having this ordering effect:

> "Furthermore, the order within these groups is defined by an ordering
> clause, but that order only affects function evaluation, and has no effect
> on the order in which rows are returned by the query."

> The behaviour is unexpected from my perspective, but obviously there are
> workarounds.  Is anyone able to confirm any of this?

AFAIR, others have already pointed out that without an "OR-
DER BY" clause PostgreSQL can return the result set in *any*
"order" it deems fit. So why don't you use one?

Tim