Calculating rank not working - Mysql

Question

I have a DB Table user_points which contains user's points and I am trying to calculate ranking based on points. It is working fine for all users except users having 1 point.

If user have 1 point it is showing it's rank as 0 but it should display it's rank as last or in last numbers like: 12083 etc.

Higher points are, ranking should be higher as well. For example:

1000 points = rank 1

1 point = rank 1223

Following is the query.

SELECT id, mobileNo, points, 
       FIND_IN_SET( points, (SELECT GROUP_CONCAT( points ORDER BY points DESC ) 
                             FROM users_points )) AS rank 
FROM users_points 
WHERE mobileNo = '03214701777'

What should I change to fix it?

Define "working fine" vs. "not working fine". What is the data? What is the result? What is the desired result? — David, Jun 20 '18 at 13:47
What's the breakpoints for each rank? Why is rank 1223 exactly 1 point and rank 1 exactly 1000 points? — Qirel, Jun 20 '18 at 13:49
What happen if two users have the same points? My guess is lot of user have 1 points so the order of those is random — Juan Carlos Oropeza, Jun 20 '18 at 13:49
So you are asking how one would query to get where you are on the "rank-ladder", based on how many points you have? The one with most points is rank 1, the one with least points is last, sorted as such? — Qirel, Jun 20 '18 at 13:52
look at the answers to this question https://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select — Marshall Tigerus, Jun 20 '18 at 13:56
answer there is wrong for this question - if 2 entities have the same points/ordercount they will get different rankings. — symcbean, Jun 20 '18 at 13:58

score 3 · Accepted Answer · answered Jun 20 '18 at 13:56

3

SELECT a.id, a.mobileNo, a.points, 
   IFNULL((SELECT COUNT(*) AS rank
    FROM users_points b
    WHERE b.points<a.points), 0)+1 as rank
FROM user_points a
WHERE a.mobileNo  = '03214701777'

Seems to be what you are looking for. While it is still very innefficient it is better than your approach using FIND_IN_SET(). If you really want to use FIND_IN_SET() then you need to pad the scores to a consistent width and divide by the width+1 to get the rank.

answered Jun 20 '18 at 13:56

symcbean

47,736
6
59
94

I think it is working fine to some extent. But it is showing rank in reverse order. `Higher points (100) = Higher Rank (230)` but it should be `higher points (100) = lower rank (1)`. – Noman Ali Jun 20 '18 at 14:05
The `nullif()` is superfluous. – Gordon Linoff Jun 20 '18 at 15:09
Changing `<` to `>` worked perfectly for me :) thanks. – Noman Ali Jun 21 '18 at 05:33

Calculating rank not working - Mysql

1 Answers1