Can someone tell me why the following sql statement cannot retrieve the
maximum count of the record:

SELECT col, max(count(*)) FROM table
GROUP BY col

And also, can I use one sql statement to retrieve the maximum count of the
record? If not, how?

Thank you more!!

Hello,

you use two group function MAX and COUNT in one statement, but there is only on group by function. So the error will be "no group ..."

The count is already the maximum in the group col, but if you need the
maximum of all col the use


SELECT max(count(*)) FROM table

Hope that helps

Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com

i dont know what you exactly want to retrive using max(count(*))

actually count(*) will return only one value which is maximum.

so i think the following should work for u


SELECT col, count(*) FROM table
GROUP BY col

hi,

the only way max(count()) will have any meaning, is by getting more then 1 row from count().....
Only that case the max function will have any relevance.

example:
select count(*) from emp return one row. This is always the max
select count(*), deptno from emp group by deptno will return 3 rows. Now I have a situation in which a max would have relevance.


Hope this helps

SELECT max(count(*)) FROM table
GROUP BY col
/*that's all folks!*/