In SQL, how do I get the row with the maximum value for a specific column?

Question

I have a request:

SELECT COUNT(*) as votes, a.member_id FROM ballots a WHERE ballot_id = 1 GROUP BY a.member_id 

which gives something like:

 votes member_id 1 paul 5 mike 3 noynoy 10 andy 2 noel 

I would like to get the string "andy" because he got the highest "votes".

How do I change my query to do this?

Thanks in advance for your help :)

Answer 1

You can order it with the highest least vote using ORDER BY and then DESC for descending order. Then LIMIT results fall on only one line (i.e. the highest).

 SELECT COUNT(*) as votes, a.member_id FROM ballots a WHERE ballot_id = 1 GROUP BY a.member_id ORDER BY votes DESC LIMIT 1 

Answer 2

 SELECT COUNT(*) as votes, a.member_id FROM ballots a WHERE ballot_id = 1 GROUP BY a.member_id ORDER BY COUNT(*) DESC LIMIT 1 

Answer 3

 SELECT COUNT(*) as votes, a.member_id FROM ballots a WHERE ballot_id = 1 GROUP BY a.member_id ORDER BY votes DESC LIMIT 1 

Answer 4

 SELECT COUNT(*) as votes, a.member_id FROM ballots a WHERE ballot_id = 1 GROUP BY a.member_id ORDER BY COUNT(*) DESC LIMIT 1 

Answer 5

Using COUNT() will not select all rows if the maximum value is repeated. My suggestion is to use MAX()

 SELECT * FROM ballots a WHERE votes = (SELECT max(votes) FROM ballots) 

Answer 6

it might be easier to get a dedicated view for counting votes

  CREATE VIEW v_votes AS 
   SELECT member_id, ballot_id, COUNT (*) AS votes FROM ballots GROUP BY member_id

and then use the HAVING clause

  SELECT * FROM v_votes HAVING MAX (votes)