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:

1. 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
);

All Problems

All Solutions