CODEWITHSUNDEEP
mysqldatabasekeyfield
Dennis
Dennis

Reputation: 87

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

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"

Upvotes: 3

Views: 124

Answers (3)

deluan
deluan

Reputation: 1865

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

Upvotes: 2

Joe Phillips
Joe Phillips

Reputation: 51130

SELECT username
FROM mytable
GROUP BY username
ORDER BY COUNT(1) DESC
LIMIT 1

Upvotes: 0

dugas
dugas

Reputation: 12453

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

Upvotes: 1

Related Questions