CODEWITHSUNDEEP
mysqlsql
jason
jason

Reputation: 3962

top contibuting users for particular category

I would like to find top contributors of particular state: The candidates below have gathered particular votes for that state. Find Top candidates for that states.

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'),

I am looking for:

for 

CAL
2
1
3
4
5

based on the ranks of contribution. How do I get that.

I really appreciate any help.

Thanks in Advance.

Code tried :

select uv.*, (@rank := @rank + 1) as rank
from uservotes uv,states s cross join
     (select @rank := 0) const  on uv.statesid = s.state_id
where name_state = 'CAL'
order by vote desc;

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

This is easy. You can use join and a group_concat():

select name_state, substring_index(group_concat(id order by votes desc), ',', 5)
from uservotes uv join
     states s
     on uv.state = s.state
group by name_state;

group_concat() will put all the id's in order with the highest votes first. substring_index() will extract the first five.

EDIT:

To get the top ranked users in one row, just add a where name_state = 'CA' to the above query.

To get them in different rows:

select uv.*
from uservotes uv join
     states s
     on uv.state = s.state
where state = 'CA'
order by votes desc
limit 5;

Upvotes: 1

Related Questions