RE: Using MAX with COUNT?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux