The following bug has been logged on the website:

Bug reference: 6440
Logged by: Matyas Novak
Email address: logik (AT) centrum (DOT) 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 (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

logik (AT) centrum (DOT) cz writes:
Hmm? WHERE clauses are necessarily evaluated before aggregating; that's
one of the main reasons why there's a difference between WHERE and
HAVING in the first place. Read the SQL standard, or any book about SQL.

The real reason this query isn't allowed can be found in SQL:2008
section 4.15.3 "Window functions":

Window functions may only appear in the <select list> of a <query
specification> or <select statement: single row>, or the <order by
clause> simply contained in a <query expression> that is a
simple table query.

I agree that the error message could be improved --- it'd be better if
it complained that you can't put a window function call there. You
would eventually get "window functions not allowed in WHERE clause",
but the other check is being made first.

As far as fixing your problem goes, maybe you should put the aggregate,
GROUP BY, and row_number() calls into a sub-select and put the WHERE in
the upper level. Or consider using LIMIT, which is going to be a lot
more efficient than this row_number() = 1 locution anyway.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs