>select f2 from tt where f1=1 having f2=max(f2); >Empty set (0.00 sec) >
The correct way to get the desired results in MySQL is two step: *SELECT @a:=max(f2) from tt where f1=1; *SELECT f2 from tt where f1=1 and [EMAIL PROTECTED] regds, -----Original Message----- From: Bruce Feist [mailto:[EMAIL PROTECTED] Sent: Saturday, March 08, 2003 20:40 To: MySQL List Subject: Re: HAVING behaviour Gabriel TATARANU wrote: >I had some private e-mail suggesting that MAX functions should apply to >the full table - as it is the maximum value of the field- > That is the case. Without a GROUP BY, max() is supposed to apply to the entire table. >and this is why >HAVING clause behaved in that manner. > I'm sure this is not the case since MAX does obey (as it should) >WHERE clause in the query. > To prove my point I've decided to use another data set as example: > > create table tt (f1 int, f2 int); > insert into tt values(1,1); > insert into tt values(1,2); > insert into tt values(2,3); > insert into tt values(1,4); > >select f2 from tt where f1=1 having f2=max(f2); >Empty set (0.00 sec) > > You draw the conclusions. > In ANSI-compliant SQL, I do not think that it is legal to compare f2 to max(f2), because using an aggregate function works only after summarization, while referring to the detail works only before summarization. I am surprised that MySQL accepts the syntax at all. The claim that HAVING is executed after WHERE is usually correct (always, in ANSI-compliant SQL, I believe). I think that MySQL has extended the syntax in a useful but non-standard way which results in it violating this rule. The "normal" flow would be: 1) Evaluate and apply WHERE clause 2) Summarize data according to the GROUP BY, or if there is no GROUP BY generate a single summary row for the entire table. 3) Evaluate and apply HAVING clause. This *cannot* be used in the query you have above, because f2 on its own is only meaningful before summarization and max(f2) is only meaningful after summarization, yet you are comparing them. In order to evaluate this, MySQL is apparently doing the equivalent of a subquery: 1) Evaluate max(f2) for the entire table as a psuedo-subquery and remember the result 2) Evaluate and apply WHERE clause to table f2. 3) Since there is no more summarization to be done, treat the HAVING as a WHERE clause, and evaluate and apply it to the individual rows, using result remembered from (1). And those are the conclusions I draw. Bruce Feist --------------------------------------------------------------------- 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
