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

Question

Hi :)

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

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 2

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 3

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 4

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

Answer 5

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

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

Answer 6

If you use Limit 1 how will you know if there was a Tie for first?

SELECT      
     Count_Result.votes,
     Count_Result.member_id   
FROM
     (Select        
          Count(*)  as votes, 
          a.member_id   
     FROM       
          ballots a
     WHERE 
          ballot_id = 1     
     GROUP BY 
          a.member_id) Count_Result     
     INNER JOIN 
     (SELECT            
          MAX(votes)votes
     FROM       
          (Select 
               Count(*)  as votes, 
               a.member_id          
          FROM
               ballots a
          WHERE 
               ballot_id = 1            
          GROUP BY 
               a.member_id ) tr     
          )     max_votes 
     ON Count_Result.votes = max_votes.votes

Alternative using the having clause

Select 
    Count(*)  as votes, 
    a.member_id   
FROM
    ballots  a
WHERE
    ballot_id = 1 
GROUP BY
    a.report_date
HAVING 
    Count(*) = 
    (SELECT            
        MAX(votes)votes
    FROM       
        (Select 
                Count(*)  as votes, 
                a.member_id   
        FROM
                ballots  a
        WHERE 
            ballot_id = 1 
        GROUP BY 
            a.member_id   e ) tr     
          )    

Answer 7

might be easier to get with a dedicated view for counting the votes

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

and then use a HAVING clause

SELECT * FROM v_votes HAVING MAX(votes)