CODEWITHSUNDEEP
mysql
Raees Khan
Raees Khan

Reputation: 369

How to get a row with maximum value for a column

This MySQL query is returning 3 rows, but I want to select only that row where total_votes = max(total_votes); please tell what should I do for that?

SELECT 
      assembly, 
      seat_code, 
      CONCAT(first_name, ' ', last_name), 
      total_votes, 
      party_id      
FROM (polling JOIN seat USING (seat_id) JOIN candidate USING (candidate_id))
WHERE seat_id = 1;

Upvotes: 0

Views: 33

Answers (2)

Anil Baweja
Anil Baweja

Reputation: 150

Replace your query from 

SELECT 
      assembly, 
      seat_code, 
      CONCAT(first_name,' ',last_name), 
      total_votes, 
      party_id      
FROM (polling JOIN seat USING (seat_id) JOIN candidate USING (candidate_id))
WHERE seat_id=1;

to

SELECT 
      assembly, 
      seat_code, 
      CONCAT(first_name,' ',last_name), 
      total_votes, 
      party_id      
FROM (polling JOIN seat USING (seat_id) JOIN candidate USING (candidate_id))
WHERE seat_id=1 AND total_votes=(SELECT MAX(total_votes) FROM <table name>);

Upvotes: 2

Rigel1121
Rigel1121

Reputation: 2034

You can use the MAX aggregate:

SELECT 
      assembly, 
      seat_code, 
      CONCAT(first_name,' ',last_name)name, 
      MAX(total_votes)total_votes, 
      party_id      
FROM polling JOIN seat USING (seat_id) JOIN candidate USING (candidate_id)
WHERE seat_id=1;

NOTE: There is no need to put parentheses in the JOIN Section.

Upvotes: 0

Related Questions