LeetCode 574. Winning Candidate SQL Server

create table candidate(id int,Name varchar(2))

insert into candidate values (1,'A')
insert into candidate values (2,'B')
insert into candidate values (3,'C')
insert into candidate values (4,'D')
insert into candidate values (5,'E')

CREATE TABLE Vote (id int,CandidateId int)
insert into Vote values(1,2)
insert into Vote values(2,4)
insert into Vote values(3,3)
insert into Vote values(4,2)
insert into Vote values(5,5)


select * from candidate
select * from Vote

Write a sql to find the name of the winning candidate, the above example will return the winner B.

select Name from candidate c join 
(
select top 1 CandidateId from Vote
group by CandidateId 
order by count(*) desc
) as w
on c.id=w.CandidateId

Write a sql to find the name of the winning candidate, the above example will return the winner B.

select Name from candidate c join 
(
select top 1 CandidateId from Vote
group by CandidateId 
order by count(*) desc
) as w
on c.id=w.CandidateId

with cte as(
select TOP 1 CandidateId,count(CandidateId) as cnt
from Vote
group by CandidateId
order by cnt desc
)select c.Name
from cte join candidate c on cte.CandidateId=c.id