CODEWITHSUNDEEP
mysqlsqlrank
John Zhang
John Zhang

Reputation: 401

mysql rank and subtraction of count

I have a table to store the votes. I query out the rank of candidates, and I also want the candidate to see how many votes are required to equal the votes held by the candidate ranked immediately above them. 

 CREATE TABLE `vote` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `candidateid` int(11) NOT NULL,
 `openid` varchar(2048) NOT NULL,
 `weight` int(11) DEFAULT '1',
 `time` bigint(20) DEFAULT NULL,
 `date` varchar(56) DEFAULT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8;



      select t.* ,  @curRank := @curRank + 1 AS rank
        from 
        (
        SELECT    candidateid,
                count(*) as num
        FROM      vote p
        group by candidateid
        ORDER BY  num desc
        ) t, (SELECT @curRank := 0) r

As far as I got 

candidateid  num    rank
1            42     1
6            16     2
8            9      3
2            3      4
7            1      5
4            1      6

I want to get 

candidateid  num  sub  rank
1            42   0    1
6            16   26   2
8            9    7    3
2            3    6    4
7            1    2    5
4            1    0    6

e.g. candidateid=6 requires 26 votes to equal the candidate ranked above them. candidateid=2 only needs 6 votes to reach 9, drawing level with candidateid=8.

Upvotes: 1

Views: 164

Answers (1)

Shadow
Shadow

Reputation: 34231

Just extend your query with an additional variable to calculate the difference:

  select t.candidateid ,  @curRank := @curRank + 1 AS rank, if(@prevote=-1, 0,@prevote-t.num) as sub, @prevote:=t.num as num
    from 
    (
    SELECT    candidateid,
            count(*) as num
    FROM      vote p
    group by candidateid
    ORDER BY  num desc
    ) t, (SELECT @curRank := 0, @prevote:=-1) r

Upvotes: 1

Related Questions