Winning Candidate

You can find the winning candidate by grouping the votes by candidate and then ordering by the count of votes in descending order. Here’s the query:

1
2
3
4
5
6

SELECT c.name AS name
FROM Vote v
JOIN Candidate c ON v.candidateId = c.id
GROUP BY v.candidateId
ORDER BY COUNT(v.id) DESC
LIMIT 1;

Explanation:

  • The JOIN operation combines the Vote and Candidate tables on the candidateId and id columns, respectively.
  • The GROUP BY clause groups the results by candidateId, allowing the COUNT function to count the votes for each candidate.
  • The ORDER BY clause orders the candidates by the count of votes in descending order.
  • The LIMIT 1 restricts the result to only the candidate with the most votes, i.e., the winner.