RE: [PHP-DB] Using MAX with COUNT?

SELECT count( * ) FROM bsp_area GROUP BY boroughID ORDER BY count(*) DESC LIMIT 1 Bastien

From: sevans@bigskypenguin.com To: "Bastien Koert" <bastien_k@hotmail.com> CC: skip@bigskypenguin.com, php-db@lists.php.net Subject: RE: [PHP-DB] 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@bigskypenguin.com> To: Php-Db <php-db@lists.php.net> Subject: [PHP-DB] 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