Hi,
> Your whole query is malformed. You have an aggregate function in the
> having.
It's absolutely legal query. In fact I don't get any error messages that say
otherwise. I'm not SQL guru but I haven't heard that agregate functions have
to be excuded from HAVING clause. To quote a SQL web tutorial "HAVING... was
added to SQL because the WHERE keyword could not be used against aggregate
functions (like SUM), and without HAVING... it would be impossible to test
for result conditions."
> try:
> select f1,max(f2) from tt where f1=1 group by f1;
I thank you for your help but this query will not produce the same result as
the original query (I know, on this particular data set it will , but I can
show you a slighly more complex data set that shows that). BTW, I've already
recoded the aplication to bypass this problem.
But all that is beside the point: HAVING clause seems to be evaluated on
original data set and not at the end of query execution as it should. Flawed
as it may be, the query on the original post demonstrated a bug of mysql in
handling HAVING clause.
Regards,
Gabriel
> >>>>How-To-Repeat:
> >>>
> >>>mysql: create table tt (f1 int, f2 int);
> >>>Query OK, 0 rows affected (0.01 sec)
> >>>
> >>>mysql: insert into tt values(1,1);
> >>>Query OK, 1 row affected (0.01 sec)
> >>>
> >>>mysql: insert into tt values(1,2);
> >>>Query OK, 1 row affected (0.00 sec)
> >>>
> >>>mysql: insert into tt values(2,3);
> >>>Query OK, 1 row affected (0.00 sec)
> >>>
> >>>mysql: select * from tt where f1=1 having f2=max(f2);
> >>>Empty set (0.00 sec)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
