CODEWITHSUNDEEP
mysqlsqldatabase
Gayan
Gayan

Reputation: 2871

Get ranking on mysql produce wrong ranks

i'm trying to get ranking based on rating percentage so mysql query like 

select c.id , sum((r.value * 20))/ count(r1.pagetypeid)  as score, @curRank := @curRank + 1 AS rank from (SELECT @curRank := 0) cr, rating as r 
inner join rateelement as r1 on r.elementid = r1.id
inner join ratesubscription as r2 on r.subscriptionid = r2.id
inner join consultant as c on r2.consultantid = c.id
where r1.displayorder not in (6) and r2.agencyid = 38
group by  c.id order by score desc

but it returns wrong raking indexes

enter image description here

what's wrong with the query?

Upvotes: 0

Views: 102

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

Ranking with variables often has issues with group by -- and even order by in the most recent versions of MySQL. So, use a subquery:

select x.*, (@curRank := @curRank + 1) AS rank
from (select c.id, sum((r.value * 20))/ count(r1.pagetypeid) as score 
      from rating r inner join
           rateelement r1
           on r.elementid = r1.id inner join
           ratesubscription r2
           on r.subscriptionid = r2.id inner join
           consultant c
           on r2.consultantid = c.id
      where r1.displayorder not in (6) and r2.agencyid = 38
      group by c.id
      order by score desc
     ) x cross join
     (SELECT @curRank := 0) cr;

Upvotes: 1

Related Questions