Can I get the highest amount of a value in a mysql database?

Question

I want to display the user with the most posts. the posts are added by counting how many times their username is displayed in the database. How can I grab all the elements and check to see which value appears more then others?

So say my database looks like this:

id | username
1 | test
2 | test
3 | no test

"test" is shown the most, so how could I say

highest poster: "test"

Answer 1

This query returns username and number of occurrences, sorted in reverse order, so the first record is the one with more occurrences:

select username, count(id) from tablename 
group by username
order by count(id) desc

UPDATE: As pointed by thedugas and Joe Phillips, you can add a limit 1 clause to this query to get only the record with the highest number of occurrences

Answer 2

select username, count(id) as uc
from tableName
group by username
order by uc desc
limit 1