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