LeetCode: Winning Candidate Posted on February 16, 2018July 26, 2020 by braindenny Winning Candidate Similar Problems: CheatSheet: SQL & MySql CheatSheet: Leetcode For Code Interview CheatSheet: Common Code Problems & Follow-ups Tag: #sql Table: Candidate +-----+---------+ | id | Name | +-----+---------+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | | 5 | E | +-----+---------+ Table: Vote +-----+--------------+ | id | CandidateId | +-----+--------------+ | 1 | 2 | | 2 | 4 | | 3 | 3 | | 4 | 2 | | 5 | 5 | +-----+--------------+ id is the auto-increment primary key, CandidateId is the id appeared in Candidate table. Write a sql to find the name of the winning candidate, the above example will return the winner B. +------+ | Name | +------+ | B | +------+ Notes: You may assume there is no tie, in other words there will be at most one winning candidate. Github: code.dennyzhang.com Credits To: leetcode.com Leave me comments, if you have better ways to solve. ## https://code.dennyzhang.com/winning-candidate select Name from Candidate where id = (select CandidateId from Vote group by CandidateId order by count(1) desc limit 1) ## Assumtion: if we have two candidates with the same votes, we choose the one who get the first vote # select Name # from Candidate inner join # (select CandidateId # from Vote # group by CandidateId # order by count(1) desc # limit 1) as t # on Candidate.id = t.CandidateId Post Views: 5