Hi,

If I do the query:
SELECT COUNT(*) FROM Persons GROUP BY LastName;
I get a listing giving the count from each group.

I want to determine the largest group so I tried:
SELECT MAX(COUNT(*)) FROM Persons GROUP BY LastName;
The above query, according to MySQL, is not valid.

I would appreciate help.

Thank you,
Joe

On 12-05-2011 19:31, Joseph Hesse wrote:
SELECT MAX(cnt)
FROM ( SELECT COUNT(*) cnt
FROM Persons
GROUP BY LastName ) t


--
Luuk

On 2011-05-12 19:31, Joseph Hesse wrote:
Even if it where valid, the meaning would be kinda weird. Since you are
grouping by lastname, you would get max per lastname, which in turn
would be the same as count.

The result of:

SELECT COUNT(*) as cnt
FROM Persons
GROUP BY LastName

is a new table so you can select from that:

select max(cnt) from (
SELECT COUNT(*) as cnt
FROM Persons
GROUP BY LastName
) as T

/Lennart

Joseph Hesse <joehesse (AT) gmail (DOT) com> wrote:
SELECT LastName,
COUNT(*) AS cnt
FROM Persons
GROUP BY LastName
ORDER BY cnt DESC
LIMIT 1;