574. Winning Candidate


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 at most one winning candidate.


b'
\n
\n\n
\n

Solution

\n
\n

Approach: Using JOIN and a temporary table [Accepted]

\n

Algorithm

\n

Query in the Vote table to get the winner\'s id and then join it with the Candidate table to get the name.

\n

MySQL

\n
SELECT\n    name AS \'Name\'\nFROM\n    Candidate\n        JOIN\n    (SELECT\n        Candidateid\n    FROM\n        Vote\n    GROUP BY Candidateid\n    ORDER BY COUNT(*) DESC\n    LIMIT 1) AS winner\nWHERE\n    Candidate.id = winner.Candidateid\n;\n
\n
'