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

Question

I have a query:

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

which yields something like:

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

I'd like to be able to get the row "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 from highest to lowest votes using ORDER BY and then DESC for descending order. Then LIMIT the results to only one row (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