2008/12/31 Tom Lane <tgl@sss.pgh.pa.us>:
> "Pavel Stehule" <pavel.stehule@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 | 511 | 3 | 16 | 1112 | 3 | 16 | 1216 | 3 | 16 | 1616 | 3
| 16 | 1616 | 3 | 16 | 1622 | 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 | 511 | 3 | 16 | 2212 | 3 | 16 | 2216 | 3 | 16 | 2216 | 3
| 16 | 2216 | 3 | 16 | 2222 | 3 | 16 | 22
(13 rows)
it should be noticed in doc?
regards
Pavel Stehule
> regards, tom lane
>
