CODEWITHSUNDEEP
mysqlrank
PongGod
PongGod

Reputation: 859

Problems with Rank logic in MySql

Having followed some examples posted in SO, namely this one, Multiple ranks in one table, I've attempted to apply this approach to my own situation, but I'm not getting the results I'm expecting. Here is my query:

  select r.memberid, r.final_rating,le.event_date, le.id, le.league_id,
(CASE r.memberid WHEN @curMember THEN @curRow := @curRow+1 ELSE @curRow := 1 AND @curMember := r.memberid END) AS rank
from rating r
inner join league_event le on r.league_event_id=le.id
inner join (SELECT @curRow := 0, @curMember := 0) x
where r.memberid IS NOT NULL
order by r.memberid, le.event_date desc, le.id desc

And here is the output:

enter image description here

What I'm trying to do is to group my records by memberid and then within those groups display the records in descending order by event_date and id (of the event). But what's apparently happening is that the rank keeps resetting within the set of a member's records each time the league_id changes even though I've not included league_id anywhere in my query other than the select clause. What the heck is going on here?

Upvotes: 0

Views: 58

Answers (1)

moni_dragu
moni_dragu

Reputation: 1163

I had the same issue when I used this approach on joined tables, so I had to adapt it. I use a subquery to build the desired output and apply rank calculation on the result:

SELECT rs.*,(CASE rs.memberid WHEN @curMember THEN @curRow := @curRow+1 ELSE @curRow := 1 AND @curMember := rs.memberid END) AS rank
FROM (
   SELECT r.memberid, r.final_rating,le.event_date, le.id, le.league_id
   FROM rating r INNER JOIN league_event le ON r.league_event_id=le.id
   WHERE r.memberid IS NOT NULL
   ORDER BY r.memberid, le.event_date DESC, le.id DESC
) rs, (SELECT @curRow := 1, @curMember := 0) x
ORDER BY rs.memberid, rs.event_date DESC, rs.id DESC

Upvotes: 1

Related Questions