Search Postgresql Archives

Window function order changing order of whole query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I tried posting this on the pgsql-sql but they don't seem to be posting successfully there, so I'm re-posting it here.

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?

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?

Thanks

Thom

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux