2008/12/31 Tom Lane <t...@sss.pgh.pa.us>: > "Pavel Stehule" <pavel.steh...@gmail.com> writes: >> I didn't expect so ORDER can change result of function sum. > > Read the stuff about window frames. The results you show are > exactly per spec. >
I have to do it, when I tested last_value and first_value function I was surprised more - order by changing partitions postgres=# select a, b, last_value(a) over (partition by b), last_value(a) over (partition by b order by a) from foo; a | b | last_value | last_value ----+---+------------+------------ 1 | 1 | 5 | 1 1 | 1 | 5 | 1 2 | 1 | 5 | 2 2 | 1 | 5 | 2 4 | 1 | 5 | 4 4 | 1 | 5 | 4 5 | 1 | 5 | 5 11 | 3 | 16 | 11 12 | 3 | 16 | 12 16 | 3 | 16 | 16 16 | 3 | 16 | 16 16 | 3 | 16 | 16 22 | 3 | 16 | 22 (13 rows) so I have to modify query to get expected values postgres=# select a, b, last_value(a) over (partition by b), last_value(a) over (partition by b order by a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from foo; a | b | last_value | last_value ----+---+------------+------------ 1 | 1 | 5 | 5 1 | 1 | 5 | 5 2 | 1 | 5 | 5 2 | 1 | 5 | 5 4 | 1 | 5 | 5 4 | 1 | 5 | 5 5 | 1 | 5 | 5 11 | 3 | 16 | 22 12 | 3 | 16 | 22 16 | 3 | 16 | 22 16 | 3 | 16 | 22 16 | 3 | 16 | 22 22 | 3 | 16 | 22 (13 rows) it should be noticed in doc? regards Pavel Stehule > regards, tom lane > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
