Find the top 2 Candidate who have the highest vote

By | November 4, 2015

Try this query for getting the highest two records.

select * from 
       (select constituency_name,candidate_name,party_name,votes,party_id,constituency_id,
            @a :=if(@b=constituency_id,@a+1,1)as a,
            @b:=constituency_id
      from 
      political_candidates 
     order by constituency_id,votes desc ) as demo where a<=2
@b:=constituency_id
This variables used in a query to do calculations and to store intermediate results.

This line is executed for each row and stores the value of constituency_id column to @a variable.
 @a :=if(@b=constituency_id,@a+1,1)as a
@a is the increment rank, otherwise set it to 1

Leave a Reply

Your email address will not be published. Required fields are marked *