Dramalyse Vor Dramalyse Vor - 1 year ago 102
MySQL Question

MySQL - get max value of count

I'm trying to only select Nicknames with the maximum number of posts but I can't seem to be able to get it done...
Needless to say the code below doesn't work but this is what I got so far.

SELECT Person.Nickname FROM Posting, Person
WHERE Person.Nickname=Posting.Nickname
AND count(Posting.PostingID)=(select max(count(Posting.PostingID)))
GROUP BY Person.Nickname
ORDER BY Person.Nickname ASC;

It would be great if someone could help!
I'm slowely but surely getting REALLY frustrated and I feel that my problem is something really easy that I'm just overlooking...

Thx in advance for your help!

Edit: This is under the assumption that there is more than one person with the maximum amount of posts.
For example: A might have 5 Posts, B might have 4, C might have 5 Posts and so on. The I would like for the output to be: A,C,...

Answer Source

A simple way is based on limit of the select count(..)

SELECT Person.Nickname, count(Posting.PostingID) 
FROM Posting, Person 
GROUP BY Person.Nickname 
ORDER BY count(Posting.PostingID)  DESC limit 1    ;

If you have more then a person with max(count) then

SELECT Person.Nickname
FROM Posting, Person 
GROUP BY Person.Nickname 
having  count(Posting.PostingID)   = (SELECT max(count(Posting.PostingID) )
                                    FROM Posting, Person  )  ;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download