Thread: window function and order by

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?

Thanks,
Torsten

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.

=?ISO-8859-1?Q?Torsten_F=F6rtsch?= <torsten.foertsch@gmx.net> writes:
>   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?

According to the standard, you'd need the ORDER BY to guarantee any
particular ordering of the output.  However, our implementation will
deliver the rows in the window function's order as long as there's
only one window function order (otherwise you get a more-or-less-
random one of them...).  It won't cost anything to have the ORDER BY
though, so I'd recommend keeping it.

            regards, tom lane