DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:content-type;
	b=g5NXuX31c4e6p9ZZNme6+6KrXjepqObjxDKcdfpF+WiUHOs4s0gQYjYnN9TmAFgcHK
	oElzITsiuU5axBm5OfvRBLKQ2MYeFPwtEd2aRkydslabHmPJ0u7cYfH0mHaaD1rwY4HV
	oCnhjeyHDhGCjFMUIwVVwAY08kiA52ZrN4CT8=
MIME-Version: 1.0
In-Reply-To: <bddc86150910091726s704277f9u7ddcead9874889b3@mail.gmail.com>
References: <bddc86150910091726s704277f9u7ddcead9874889b3@mail.gmail.com>
From: Thom Brown <thombrown@gmail.com>
Date: Mon, 12 Oct 2009 08:09:42 +0100
Message-ID: <bddc86150910120009p3ae9d578x2dac139a71e4d045@mail.gmail.com>
Subject: Re: Lag and lead window functions order by weirdness
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=000e0ce044b44ec48b0475b79e0b

--000e0ce044b44ec48b0475b79e0b
Content-Type: text/plain; charset=ISO-8859-1

2009/10/10 Thom Brown <thombrown@gmail.com>

> I've had a look at examples of lag and lead window functions with order by
> inside the OVER clause, and I'm confused as to why it influences the overall
> order in the output.
> For example (for a table called category with incrementing id numbers up to
> 26):
>
> SELECT id, lag(id) OVER (ORDER BY id DESC) FROM category;
>
> This would yield:
>
> id   lag
> 26   NULL
> 25   26
> 24   25
>
> This is the equivalent of what we would get with:
> SELECT id, lead(id) OVER (ORDER BY id ASC) FROM category ORDER BY id DESC;
>
> I would expect the row order in the results not to be influenced by the
> OVER clause's ORDER BY as I thought that was just to determine how the
> aggregate window function's values would be output.  It's almost as if the
> main part of the query has inherited it's order from the OVER clause.
>
> Am I missing something here?
>
> Thanks
>
> Thom
>

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?

Thom

--000e0ce044b44ec48b0475b79e0b
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div class=3D"gmail_quote">2009/10/10 Thom Brown <span dir=3D"ltr">&lt;<a h=
ref=3D"mailto:thombrown@gmail.com">thombrown@gmail.com</a>&gt;</span><br><b=
lockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px =
#ccc solid;padding-left:1ex;">

I&#39;ve had a look at examples of lag and lead window functions with order=
 by inside the OVER clause, and I&#39;m confused as to why it influences th=
e overall order in the output.<div><br></div><div>For example (for a table =
called category with incrementing id numbers up to 26):</div>


<div><br></div><div>SELECT id, lag(id) OVER (ORDER BY id DESC) FROM categor=
y;</div><div><br></div><div>This would yield:</div><div><br></div><div>id =
=A0 lag</div><div>26 =A0 NULL</div><div>25 =A0 26</div><div>24 =A0 25</div>=
<div>


<br></div><div>This is the equivalent of what we would get with:</div><div>=
SELECT id, lead(id) OVER (ORDER BY id ASC) FROM category ORDER BY id DESC;<=
/div><div><br></div><div>I would expect the row order in the results not to=
 be influenced by the OVER clause&#39;s ORDER BY as I thought that was just=
 to determine how the aggregate window function&#39;s values would be outpu=
t. =A0It&#39;s almost as if the main part of the query has inherited it&#39=
;s order from the OVER clause.</div>


<div><br></div><div>Am I missing something here?</div><div><br></div><div>T=
hanks</div><div><br></div><font color=3D"#888888"><div>Thom</div>
</font></blockquote></div><br><div>Having a look around, it looks as if Pos=
tgres might be misbehaving. =A0According to this page,=A0<a href=3D"http://=
my.safaribooksonline.com/0596004818/sqlnut2-CHP-4-SECT-3">http://my.safarib=
ooksonline.com/0596004818/sqlnut2-CHP-4-SECT-3</a>, the ORDER BY in the win=
dow function&#39;s clause shouldn&#39;t be having this ordering effect:</di=
v>

<div><br></div><div>&quot;Furthermore, the order within these groups is def=
ined 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.&q=
uot;</div>

<div><br></div><div>The behaviour is unexpected from my perspective, but ob=
viously there are workarounds. =A0Is anyone able to confirm any of this?</d=
iv><div><br></div><div>Thom</div>

--000e0ce044b44ec48b0475b79e0b--