I have this query that is trying to make a ranking from the total number of positive votes, that each user is getting. The problem is that the ranking is made by THE USER ID, not by VOTES. The problem is because of GROUP BY, but I have to use it because one id appears multiple times in voted_id section.
I wonder if you have some solutions?
select
a.voted_id,
(select
count(b.state)
from
user_vote as b
where
b.state=1 and b.voted_id=a.voted_id) as votes,
@rank:=@rank+1 as ranking from user_vote as a,
(select @rank:=0)as rank
where
a.state=1
group by
a.voted_id
order by
votes asc;
So the problem is that ranking is done by voted_id not by votes.
This any good? Little hard to see where you're at without the DDL & some sample data...
select voted_id,
votes,
@rank:=@rank + 1 as rank
from
(
select voted_id,count(*) as votes
from user_vote
where state=1
group by voted_id
order by votes desc
) t
join (select @rank:=0) r;