Winning Candidate 24-9-22

link - https://www.codingninjas.com/codestudio/problems/winning-candidate_2119331?topList=top-100-sql-problems&leftPanelTab=0

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


#Solution 1:
SELECT Name FROM Candidate 
JOIN
(SELECT CandidateID
FROM Vote
GROUP BY CandiateId
ORDER BY Count(*) DESC
lIMIT 1) AS T1
ON Candidate.id=T1.CandidateId

#Solution 2
with ne (ca_id) as (select id from Vote 
                    group by id 
                   order by count(*) desc
                   limit 1)

select Name "Name" from Candidate
where id = (select ca_id from ne)