Re: Window function order changing order of whole query

On Mon, Oct 12, 2009 at 09:13:09AM +0100, Thom Brown wrote:
> 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.

Given that you didn't specify an order for the outer query, postgres is
allowed to give you the rows in any order it likes. In this case that
happens to be the order you see. If you would like another order you
need to specify it.

> 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;

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.