The following bug has been logged on the website: Bug reference: 6440 Logged by: Matyas Novak Email address: lo...@centrum.cz PostgreSQL version: 9.1.1 Operating system: Linux Description:
Window function in WHERE clause - as all other functions used in where - should be evaluated before agregating takes place. But it seems that postgresql try evaluate its after agregating, as it'd be in HAVING clause. E.g. in folowing example I'l try to sum the best results of given persons from all dispciplines - so I agregate over persons and best result in each discipline wann get using window function. (I'm aware that it can be done by various subselects, or that there may be better to use different window function or nested agregates, but it's simplified example from a bit complex example.) Test example: create table results ( id serial not null primary key, person integer, discipline integer, result integer ); select person, sum(result) from results where row_number() over (partition by person, discipline order by result desc) = 1 group by person Error: ERROR: column "results.result" must appear in the GROUP BY clause or be used in an aggregate function LINE 19: ...() over (partition by person, discipline order by result des... -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs