Sorry for wasting your time, I now see: If frame_end is omitted it defaults to CURRENT ROW.
clearly in the documentation. I made the assumption that the default was UNBOUNDED PROCEDING/FOLLOWING which is now very obviously not the case. Again, sorry for not really taking the time to ensure this was in fact a bug. -W On Tue, Nov 20, 2012 at 10:47 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > w...@devauld.ca writes: > > -- The last_value(0 window function appears to be changing values > > -- mid window. An example is detailed below. > > So far as I can see, these results are correct per spec. Note the > caution in the manual about these window functions: > > Note that first_value, last_value, and nth_value consider only the rows > within the "window frame", which by default contains the rows from the > start of the partition through the last peer of the current row. This is > likely to give unhelpful results for last_value and sometimes also > nth_value. You can redefine the frame by adding a suitable frame > specification (RANGE or ROWS) to the OVER clause. See Section 4.2.8 for > more information about frame specifications. > > Since your d1 values are all distinct, the last peer is the same as the > current row, so last_value(v) ends up just producing the current row's v. > Which is what the query is producing. > > If you want the last row in the partition, you could use first_value > with the opposite sort order, or use last_value with the same sort > order and RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. > (The current SQL committee is nothing if not verbose :-() > > regards, tom lane >
