MySQL maximum counting values

Hello everybody!

I got a small problem with MySQL. As the title says, I want to get the maximum values from a count.
What I tried is the following:

This retrieves my data just as it's supposed to do. But now I want to get the MAX of all the data...
I didn't know exactly what to do. I tried to do this:

Obviously, this didn't work.
Can anybody tell me if I'm on the right track with this one? For more explanation, what I want to do is show the best visited day for my users. Just for fun, maybe there's a PHP-solution for it, but I try to do it with MySQL. I try to learn as much as I can and I think it is possible...

Thnx for the help in advance!

Cheers,

Try

SELECT count(*) as max_count FROM counter

Thnx for the reply, but I think you didn't understand my question very well.
I don't want to retrieve the total number of records of my table, I want the total of one day.
Example:
When I want to get a list of my visitors a day, I can do this:

That does the trick. But now I only want to show the day with the highest number of visitors. Or in other words, I want the max of the counted fields.

Cheers,

Oh, I see. The following should do the trick:

Hmm... it didn't...
This is my error:

You have an error in your SQL syntax near 'SELECT COUNT(*) AS counted FROM counter GROUP BY date)' at line 1

Thanks for the help though! I really appreciate it. I hope you have more ideas.

Cheers,

Seems as if your DBS doesn't support subqueries. Create a view and select from the view

CREATE VIEW view_counts AS SELECT date, count(*) AS counted FROM counter GROUP BY date

SELECT max(counted) FROM view_counts;

Hmm, didn't work either. Maybe it's time for a MySQL-update... :P

Thnx for the help, but I'll try it with PHP then.

Try to use ORDER BY xxx DESC and LIMIT 1...

select date, count(date) as thecount from counter GROUP BY date ORDER BY thecount DESC LIMIT 1

Same requirement here - MAX(COUNT(`columnname`))
It seems theres a method in MySQL 4.0 to have the MAX in a group solved using some "MAX-CONCAT trick" ?
But Im not able to get the MAX(COUNT()). Anyone can derive it from : http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html ?
Thanks