CODEWITHSUNDEEP
mysqlcountmax
Jaroslav Klimčík
Jaroslav Klimčík

Reputation: 4808

mySQL count and get maximum from table

I have a simple question. I have this table structure http://sqlfiddle.com/#!2/61cee/3/0 and I just want to count score of each player which are attacker and then pick the highest score from all players and his id. I tried this:

SELECT MAX(counts), id_player FROM 
  (SELECT COUNT(score) AS counts, id_player
   FROM fl_poll 
   WHERE position = 'attacker' 
   GROUP BY id_player) as maxi

But the result is wrong id. Where I made a mistake? Thanks for advance.

Upvotes: 1

Views: 93

Answers (1)

davek
davek

Reputation: 22895

   SELECT COUNT(score) AS counts, id_player
   FROM fl_poll 
   WHERE position = 'attacker' 
   GROUP BY id_player
   ORDER BY COUNT(score) desc
   LIMIT 1

Upvotes: 2

Related Questions