[thelist] mySQL - limit by COUNT(*)?

[rudy]

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