Le vendredi 21 juin 2013 03:32:33, Josh Berkus a écrit : > Hackers, > > So, I can create a custom aggregate "first" and do this: > > SELECT first(val order by ts desc) ... > > And I can do this: > > SELECT first_value(val) OVER (order by ts desc) > > ... but I can't do this: > > SELECT first_value(val order by ts desc) > > ... even though under the hood, it's the exact same operation.
First I'm not sure it is the same, in a window frame you have the notion of peer-rows (when you use ORDER BY). And also, first_value is a *window* function, not a simple aggregate function... See this example: # create table foo (i int, t timestamptz); # insert into foo select n, now() from generate_series(1,10) g(n); # select i, first_value(i) over (order by t desc) from foo; # select i, first_value(i) over (order by t desc ROWS between 0 PRECEDING and UNBOUNDED FOLLOWING) from foo; What do you expect "SELECT first(val order by ts desc)" to output ? -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
signature.asc
Description: This is a digitally signed message part.
