List:General Discussion« Previous MessageNext Message »
From:Tore Bostrup Date:March 5 2003 5:04pm
Subject:Re: HAVING vs. WHERE
View as plain text  
HAVING is for qualifying result rows based on the value of aggregate
functions, WHERE is for qualifying result rows based on individual (column)
values.  So in you case you should use WHERE.  Although useful in the right
situation, HAVING is used much less than a WHERE clause.

One example of how to use HAVING would be to show values that have duplicate
entries in the database:

SELECT FirstName, LastName, Count(*)
FROM MyTable
GROUP BY FirstName, LastName
HAVING Count(*) > 1

HTH,
Tore.

----- Original Message -----
From: "Jonathan Arnold" <jdarnold@stripped>
To: <mysql@stripped>
Sent: Wednesday, March 05, 2003 11:17 AM
Subject: HAVING vs. WHERE


> In the MySQL reference, it warns against using HAVING for items
> that "should" be in a WHERE clause. I'm not sure what items
> "should" be in a WHERE clause.
>
> My exact problem is I want to select some records that have a
> dotted IP address as one of the fields. And I want to filter out
> the multicast addresses, which are the addresses that begin with
> the numbers in the range of 224. thru 239. This does it:
>
> SELECT * FROM Client WHERE left(inetAdr,instr(inetAdr,"."))< 224
>      OR left(inetAdr,instr(inetAdr,".")) > 239
>
> and this works as well:
>
> SELECT *,left(inetAdr,instr(inetAdr,".")) as ia FROM Client HAVING ia <
224
>      OR ia > 239
>
> and it is a little cleaner, although as I'm going to be doing this in a
> PHP script, cleanliness isn't all that important.
>
> So I guess I have 2 questions:
>
> 1] Which should I use?
> 2] Is this the easiest way to check for the multicast address?
>
> --
> Jonathan Arnold     (mailto:jdarnold@stripped)
> Amazing Developments       http://www.buddydog.org
>
> It ain't what you don't know that gets you into trouble.
> It's what you know for sure that just ain't so.  Mark Twain
>
>
> ---------------------------------------------------------------------
> 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 <mysql-thread134036@stripped>
> To unsubscribe, e-mail <mysql-unsubscribe-tore=bostrup.us@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>

Thread
HAVING vs. WHEREJonathan Arnold5 Mar
  • Re: HAVING vs. WHEREStanimir Dzharkalov5 Mar
  • Re: HAVING vs. WHEREPaul DuBois5 Mar
  • Re: HAVING vs. WHERETore Bostrup5 Mar
  • Re: HAVING vs. WHEREBenjamin Pflugmann5 Mar
  • Re: HAVING vs. WHEREDan Nelson5 Mar