HAVING is applicable only when you are using GROUP BY clause, HAVING acts on the resultset after records are grouped while WHERE restricts the records which are used in grouping.
Manual clearly states (under WHERE optimisation) that: "HAVING is merged with WHERE if you don't use GROUP BY or group functions (COUNT(), MIN()...)." regds, -----Original Message----- From: Gabriel Tataranu [mailto:[EMAIL PROTECTED] Sent: Friday, March 07, 2003 20:50 To: [EMAIL PROTECTED] Subject: Re: HAVING behaviour Hi, this is NOT correct behavior. Where clause returns (1,1) and (1,2). The HAVING clause should be applied to what WHERE has selected and choose the (1,2) record. A quote from the manual "It (HAVING) is applied last, just before items are sent to the client, with no optimisation". This is obviously not the case here. It looks like HAVING and WHERE are both appliend to the source record set. Regards, Gabriel > This is correct. > WHERE clause returns (1,1) > HAVING eliminates all records not HAVING f2=3. > > [EMAIL PROTECTED] wrote: > >>Description: > > > > HAVING in SELECT is evaluated before WHERE. This is not the > >correct behaviour as HAVING should deal with the remainder of the WHERE > >selection (according to manual) > > > >>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
