pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Lag and lead window functions order by weirdness
2+ messages / 2 participants
[nested] [flat]
* Re: Lag and lead window functions order by weirdness
@ 2009-10-12 07:09 Thom Brown <thombrown@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Thom Brown @ 2009-10-12 07:09 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Lag and lead window functions order by weirdness
@ 2009-10-18 16:51 Tim Landscheidt <tim@tim-landscheidt.de>
parent: Thom Brown <thombrown@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tim Landscheidt @ 2009-10-18 16:51 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-10-18 16:51 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-10-12 07:09 Re: Lag and lead window functions order by weirdness Thom Brown <thombrown@gmail.com>
2009-10-18 16:51 ` Tim Landscheidt <tim@tim-landscheidt.de>
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