SELECT count( * )
FROM `bsp_area`
GROUP BY boroughID ORDER BY count(*) DESC LIMIT 1
Bastien
From: sevans@xxxxxxxxxxxxxxxxx
To: "Bastien Koert" <bastien_k@xxxxxxxxxxx>
CC: skip@xxxxxxxxxxxxxxxxx, php-db@xxxxxxxxxxxxx
Subject: RE: Using MAX with COUNT?
Date: Sat, 22 Jul 2006 20:25:57 -0600 (MDT)
Hi Bastien and all,
Bastien wrote:
> SELECT count(*) FROM `bsp_area` GROUP BY boroughID
...which returns all the counts, but I'm only interested in the one with
the most areas, so I did it like this:
SELECT count( * )
FROM `bsp_area`
GROUP BY boroughID ORDER BY count(*) DESC
...which forces the borough with the most areas to the top, so I can just
grab the first row. Probably not the best way to do it since ideally I'd
like only one row in the return set, but it gets the job done.
If anyone has a suggestion to return just the row with the most areas that
would be great.
Skip
>
>
> Bastien
>
>
>>From: Skip Evans <skip@xxxxxxxxxxxxxxxxx>
>>To: Php-Db <php-db@xxxxxxxxxxxxx>
>>Subject: Using MAX with COUNT?
>>Date: Sat, 22 Jul 2006 13:48:43 -0600
>>
>>Hey all,
>>
>>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?
>>--
>>Skip Evans
>>Big Sky Penguin, LLC
>>61 W Broadway
>>Butte, Montana 59701
>>406-782-2240
>>
>>--
>>PHP Database Mailing List (http://www.php.net/)
>>To unsubscribe, visit: http://www.php.net/unsub.php
>>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
