Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/574.html
574. Winning Candidate
Level
Medium
Description
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 only one winning candidate.
Solution
First select the CandidateId
that occurs the most in the Vote
table. Next use the CandidateId
as id
to find the candidate’s name from the Candidate
table.
# Write your MySQL query statement below
select Name from Candidate where id in (
select CandidateId from (
select CandidateId, count(*) as counts from Vote
group by CandidateId order by counts desc limit 1
) as Winner
);