CODEWITHSUNDEEP
mysqlsql
jason
jason

Reputation: 3962

my code displays wrong ranking values

I am getting wrong ranking but right values in descending order :

create table uservotes(id int, name varchar(50), vote int,state int);

INSERT INTO uservotes VALUES
(1, 'A', 34,1),
(2, 'B', 80,1),
(3, 'bA', 30,1),
(4, 'C', 8,1),
(5, 'D', 4,1),
(6, 'E', 14,2),
(7, 'F', 304,2),
(8, 'AA', 42,3),
(9, 'Ab', 6,3),
(10, 'Aa', 10,3);

States

create table states(state_id int, name_state varchar(50));

INSERT INTO states VALUES
(1, 'CA'),
(2, 'AL'),
(3, 'AZ');

using the code below and I am getting rank as 6,2,1,3,5 and not 1,2,3,4,5 why? also the value are correct not the ranks.Please let me know where my mistake is. I really appreciate any help.Thanks in Advance.

select uv.*,
      @curRank := @curRank + 1 AS rank
from  uservotes uv cross join 
     states s 
     on uv.state = s.state_id, (select @curRank := 0) const
where name_state = 'CA'
order by vote desc limit 5;

Upvotes: 0

Views: 62

Answers (1)

Shiva
Shiva

Reputation: 20955

Try this. It works as shown in this SQLFiddle: http://sqlfiddle.com/#!2/35c6c/33

select agg.*, @curRank := @curRank + 1 AS rank from
(
select uv.*
from uservotes uv cross join 
 states s 
 on uv.state = s.state_id
where name_state = 'CA'
order by vote desc limit 5) as agg, (select @curRank := 0) const;

Results:

ID  NAME    VOTE    STATE   RANK
2   B   80  1   1
1   A   34  1   2
3   bA  30  1   3
4   C   8   1   4
5   D   4   1   5

Upvotes: 3

Related Questions