Re: Using MAX with COUNT?
| From: | Stut | Date: | Sun, 23 Jul 2006 12:05:26 +0000 |
| Subject: | Re: Using MAX with COUNT? | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-42247@lists.php.net to get a copy of this message | ||
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Skip Evans wrote:
> I have a table like this:
>
> boroughID Area
> ========= ====
> 1 Chelsea
> 1 East Village
> 1 West Village
> 1 So Ho
> 2 Prospect Park
> 2 Brooklyn Heights
> 3 Prospect Heights
>
> What I want to know is which boroughID has the most area's assocated
> with it, and how many.
>
> So I tried this:
>
> SELECT max(count(*)) FROM
bsp_area GROUP BY boroughID
>
> ...and got an "Invalid use of group function" error.
>
> Anyone think of another way to do this in a single SQL statement, or
> some other simple method?
select boroughID, count(*) as thecount from bsp_area group by boroughID
order by thecount desc limit 1
- -Stut
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFEw2YG2WdB7L+YMm4RAqkyAJwIHMzDeDNBanlDpjARF4ElRasBYwCgmpIl
HGbI5JrIlTPf0c5r6Tg6+o8=
=jRmZ
-----END PGP SIGNATURE-----