kkasp kkasp - 3 months ago 9
SQL Question

mysql- Get self ranking compared to others in the score

These are one table:

mysql> SELECT * FROM test_member order by points asc;
+-----------+---------+
| member_id | points |
+-----------+---------+
| 34 | 1000 |
| 22 | 2000 |
| 33 | 2000 |
| 35 | 3000 |
+-----------+---------+


The right results:
when my member_id is '35', my rank will be '1',

when my member_id is '22', my rank will be '2',

when my member_id is '33', my rank will be '2',

when my member_id is '34', my rank will be '3',

....

and so on.

Attempt - 1

SELECT * FROM (
SELECT member_id,
@points := @points + 1 AS rank
FROM test_member p, (SELECT @points := 0) r
ORDER BY points DESC
) t
WHERE member_id='33';

+-----------+----------------------------------------+
| member_id | rank |
+-----------+------------+------+---------+----------+
| 33 | 3.000000000000000000000000000000 |
+-----------+----------------------------------------+


Attempt - 2 (mysql + php way)

<?php
$simulation_ponits = 1000;

$sql = '
SELECT t.points, COUNT(*) as count_num
FROM test_member as t
WHERE t.points > ' . $simulation_ponits . '
GROUP BY t.points
HAVING t.points > ' . $simulation_ponits .'
';

$result = mysql_query($sql);

$count_rank = 1;
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$count_rank +=$row['count_num'];
}


+--------+-----------+
| points | count_num |
+--------+-----------+
| 2000 | 2 |
| 3000 | 1 |
+--------+-----------+


# $count_rank => 3


Both attempts are only mysql + php way can show right result,
can I have other way tried to do this?
Thanks in advance.

Answer

You are attempting to get a ranking, not a row number. The following should work:

SELECT * FROM (
  SELECT    member_id,
            @rnk := @rnk + if (@prevPoints = points, 0,
                       if (@prevPoints := points, 1, 1)) as rnk
  FROM      test_member p, (SELECT @rnk := 0, @prevPoints := null) r
  ORDER BY  points DESC
) t
WHERE member_id = 33