[thelist] mySQL - limit by COUNT(*)?
rudy
r937 at interlog.com
Wed Feb 5 23:48:05 CST 2003
R.Livsey wrote:
> SELECT COUNT(*) AS hits FROM requests WHERE hits>10 GROUP BY request
paul replied:
> use HAVING.
correct
i also answered this on the, um, other list that mister livsey posted this
question on, and not here, because of the list hiccough today
say, where's the official explanation of that, anyway? we are all curious
perhaps the list admins were so strenuously overtaxed in fixing whatever it
was that they went straight to bed, and will tell us later
paul continued:
> You might be able to use
> HAVING hits > 10
> instead of
> HAVING COUNT(*) > 10
> but you may also not. You can't in MS SQL 7, anyway.
nor in any others, off the top of my head
paul, i'm not trying to show you up, but you missed something fairly obvious
in the original query, even once the syntax has been corrected:
select count(*) as hits
from requests
group by request
having count(*) > 10
this query will return results similar to:
45
73
84
12
56
tell me, which requests were those hits for?
that's right, you need to include the request column in the SELECT list
not a syntactic error, but a semantic one ;o)
so the query should be
select request, count(*) as hits
from requests
group by request
having count(*) > 10
rudy
More information about the thelist
mailing list