Infibeam Interview Question
SDE-2sCountry: India
Interview Type: Phone Interview
use this query:
select v.CandidateId, max(v.Num) as Votes from
(select CandidateId, count(CandidateId) as Num from Vote group by CandidateId) v
group by v.CandidateId
select c.condidateId,c.name as condidate_name,count(v.Id) as no_of_votes from vote v
inner join candidate c
on v.candidateId=c.id
group by c.condidateId,c.name
having no_of_votes=(select max(no_of_votes) from (
select condidateId,count(Id) as no_of_votes from vote group by condidateId) A)
OR
select c.condidateId,c.name as condidate_name,count(v.Id) as no_of_votes from vote v
inner join candidate c
on v.candidateId=c.id
group by c.condidateId,c.name
having no_of_votes=(select condidateId,count(Id) as no_of_votes from vote group by condidateId order by no_of_votes desc limit 1)
it just returns the name of the winner and the votes he got
- narasimha June 13, 2014