Re: window function and order by

Torsten Förtsch wrote
> Hi,
>
> assuming I have a query that computes a running sum like this:
>
> select id, buy_price, sum(buy_price) over (order by id) sum
> from fmb
> where 202300<=id and id<=202400
> order by id;
>
> Do I need the ORDER BY clause at the end? Or does the ORDER BY in the
> window function already define the order of the result set?

It is possible for the final output to be out-of-order e.g. (2, 3, 1) but
the running total associated with each row will be correct. IOW, if you
were to manually perform an order-by on the result you would get the same
result as if you have included the outer (non-window) order-by in the
original query.

Input:
(1, 1), (2, 2), (3, 3)

Possible Output:
(2, 2, 3), (3, 3, 6), (1, 1, 1)

Expected Output:
(1,1,1), (2,2,3), (3,3,6)

Not Possible:
(2, 2, 2), (3,3,5),(1,1,6)

If you want the output in a specific order you should specify that order
explicitly. By coincidence, with simple queries, you may consistently get
the expected results but that is not something to rely upon.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/window-function-and-order-by-tp5784285p5784292.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.