On Oct17, 2011, at 00:14 , Tom Lane wrote:
> Florian Pflug <fgp@phlo.org> writes:
>> But some frame clauses (row 1 preceding, for example) have an effect despite
>> there being no ORDER BY, like here:
>
> Yeah, why did you expect differently? Without ORDER BY, all rows are
> peers in the frame ordering, so there's no way for a RANGE spec to
> select less than the whole partition. But with ROWS, you can select
> less than that.
I was confused by
When an aggregate function is used as a window function, it aggregates over the rows within the current row's window
frame.[3.5, Window Functions].
combined with the part I quoted before, which was
By default, if ORDER BY is supplied then the frame consists of all rowsfrom the start of the partition up through the
currentrow, plus anyfollowing rows that are equal to the current row according to the ORDER BYclause. When ORDER BY is
omittedthe default frame consists of all rowsin the partition. [9.19, Window Functions, Last Paragraph]
But, reading those parts again, I realize the it says "When ORDER BY is omitted
the *default* frame consists ... ", and that the second quote is followed
by a footnote which says
There are options to define the window frame in other ways, but this tutorial does not cover them. See Section 4.2.8
fordetails. [3.5, Window Functions]
So it was just me being thick. Sorry for the noise.
best regards,
Florian Pflug