Winning Candidate

Problem

You are given two tables. Candidate lists every person who can be voted for; Vote records one row per ballot cast.

Table: Candidate

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| name        | varchar  |
+-------------+----------+
id is the PRIMARY KEY (column with unique values).
Each row holds the id and name of one candidate.

Table: Vote

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| candidateId | int  |
+-------------+------+
id is an auto-increment PRIMARY KEY (column with unique values).
candidateId is a FOREIGN KEY referencing Candidate.id.
Each row records the candidate who received the i-th vote.

Goal

Report the name of the winning candidate — the one who received the largest number of votes. The test cases are generated so that exactly one candidate wins, so you never have to break a tie.

The shape of the answer

This is the classic count-then-rank pattern that shows up everywhere in SQL: pair a child table against its parent, count the children per parent, sort by that count, and keep the top one. Read the four moving parts before the query so the SQL reads like prose:

• JOIN ties each vote back to the candidate who earned it.
• GROUP BY collapses all of one candidate's vote rows into a single row.
• COUNT measures the size of each group — that is the vote tally.
• ORDER BY … DESC + LIMIT 1 ranks the tallies and keeps the largest.

The only subtle decision is which side of the join to start from and which column to group on. Both choices below are deliberate, and the Pitfalls section explains why.

Solution (MySQL 8)

The whole problem is four clauses. This page targets MySQL 8; the dialect matters for the grouping rules discussed below, and for the -- comment requiring a trailing space.

-- Winning candidate: count votes per candidate, take the top one
SELECT   c.name
FROM     Candidate c
         LEFT JOIN Vote v ON v.candidateId = c.id
GROUP BY c.id
ORDER BY COUNT(v.id) DESC
LIMIT    1;

Two deliberate choices make this both correct and strict-mode-clean:

• Group by c.id, not c.name. id is the primary key, so it is guaranteed unique. Grouping on it can never accidentally merge two different candidates who happen to share a name.
• Select c.name while grouping by c.id. Because c.id is the primary key, every other column of Candidate — including name — is functionally dependent on it: one id determines exactly one name. MySQL 8 recognizes this, so the query is valid under ONLY_FULL_GROUP_BY with no extra wrapping.

Walking through it step by step

Step 1 — LEFT JOIN

Start from Candidate and LEFT JOIN Vote. The left join is what guarantees every candidate appears even with zero votes — their vote columns simply come back NULL. A plain INNER JOIN would silently drop any candidate nobody voted for.

+------+--------+------+---------------+
| c.id | c.name | v.id | v.candidateId |
+------+--------+------+---------------+
|  1   |  A     | NULL | NULL          |  -- A got no votes
|  2   |  B     |  1   |  2            |
|  2   |  B     |  4   |  2            |  -- B appears once per vote
|  3   |  C     |  3   |  3            |
|  4   |  D     |  2   |  4            |
|  5   |  E     |  5   |  5            |
+------+--------+------+---------------+

Notice candidate B occupies two rows — one per vote received. That multiplicity is exactly what the next step counts.

Step 2 — GROUP BY c.id

GROUP BY c.id collapses all rows that share a candidate id into a single output row. After this, there is one row per candidate, and we can ask an aggregate question of each group.

+------+--------+
| c.id | c.name |
+------+--------+
|  1   |  A     |
|  2   |  B     |   -- B's two vote rows are now one group
|  3   |  C     |
|  4   |  D     |
|  5   |  E     |
+------+--------+

Step 3 — ORDER BY COUNT(v.id) DESC

COUNT(v.id) counts how many non-NULL vote ids fell into each group — that is each candidate's tally. Counting v.id rather than * matters: for candidate A the joined v.id is NULL, so COUNT(v.id) is correctly 0, whereas COUNT(*) would wrongly report 1. DESC puts the biggest tally on top.

+--------+----------------+
| c.name | COUNT(v.id)    |
+--------+----------------+
|  B     |  2             |  <- winner floats to the top
|  C     |  1             |
|  D     |  1             |
|  E     |  1             |
|  A     |  0             |
+--------+----------------+

Step 4 — LIMIT 1

The list is already sorted with the winner first, so LIMIT 1 simply keeps the top row.

+--------+
| name   |
+--------+
|  B     |
+--------+

Pitfalls

• Use COUNT(v.id), not COUNT(*). With a LEFT JOIN, a candidate with no votes still produces one row whose vote columns are NULL. COUNT(*) counts that phantom row as 1; COUNT(v.id) ignores NULLs and reports the true 0.
• Group by the unique c.id, not c.name. Two distinct candidates could share a display name. Grouping by name would merge their tallies into one inflated total; grouping by the primary key keeps every candidate separate.
• ONLY_FULL_GROUP_BY is satisfied here for free. Because c.id is the primary key, c.name is functionally dependent on it, and MySQL 8 explicitly permits selecting c.name while grouping by c.id — no ANY_VALUE() is required, and the query above runs unchanged under strict mode. You would only reach for ANY_VALUE() in the other scenario: grouping by a non-unique column (e.g. GROUP BY name) and then selecting a column that is not determined by it — there MySQL cannot pick a single value per group, so strict mode rejects the bare column unless you wrap it.
• Don't rely on LIMIT 1 to resolve ties. This problem guarantees a single winner, so LIMIT 1 is safe. If ties were possible and you needed all top candidates, you would switch to a RANK()/DENSE_RANK() window function or an = MAX(...) subquery instead.
• Mind the -- comment rule. In MySQL, a -- line comment requires a trailing space (or newline) after the dashes; --comment without the space is a syntax error.

Source

Problem adapted from LeetCode 574, Winning Candidate. Grouping and strict-mode behavior verified against the MySQL 8 Reference Manual, section 12.20.3 — MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause… provided they are functionally dependent on the GROUP BY columns, with ANY_VALUE() documented (section 14.18.1) as the escape hatch for the non-dependent case. Comment syntax per the MySQL 8 manual, section 11.7 (the -- sequence must be followed by whitespace).