S.M_Emamian S.M_Emamian - 5 months ago 46
SQL Question

How to execute a custom query in codeigniter - rank of a user in a score table

I have a table like this:

id user_id score

1 16433 20
2 16433 10
3 14621 12
4 47899 10
5 13220 30
6 14621 15


my table name is
game_scores
.
now,I would like to get rank of a user (or limit of it to 8 users) in a score table:

$query = $this->db->query("

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT score,id,rank FROM
(
SELECT AA.*,BB.ID,
(@rnk:=@rnk+1) rnk,
(@rank:=IF(@curscore=score,@rank,@rnk)) rank,
(@curscore:=score) newscore
FROM
(
SELECT * FROM
(SELECT COUNT(1) scorecount,score
FROM game_scores GROUP BY score
) AAA
ORDER BY score DESC
) AA LEFT JOIN game_scores BB USING (score)) A;

");

return $query;


but it returns false .

I took this code from this link:

http://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table

Answer

you can use a query like this:

SELECT 
@rank := (@rank+1) AS rank,
sc.user_id , sc.score
FROM
(
  SELECT user_id , max(score) AS score
  FROM myscore
  GROUP BY user_id
  ORDER BY score DESC
  LIMIT 8
) AS sc
CROSS JOIN ( SELECT @rank := 0) AS param;

sample

MariaDB [yourschema]> select * from myscore;
+----+---------+-------+
| id | user_id | score |
+----+---------+-------+
|  1 |   16433 |    20 |
|  2 |   16433 |    10 |
|  3 |   14621 |    12 |
|  4 |   47899 |    10 |
|  5 |   13220 |    30 |
|  6 |   14621 |    15 |
|  7 |   47891 |    10 |
|  8 |   13222 |    30 |
|  9 |   14623 |    15 |
+----+---------+-------+
9 rows in set (0.00 sec)

MariaDB [yourschema]> SELECT
    -> @rank := (@rank+1) AS rank,
    -> sc.user_id , sc.score
    -> FROM
    -> (
    ->   SELECT user_id , max(score) AS score
    ->   FROM myscore
    ->   GROUP BY user_id
    ->   ORDER BY score DESC
    ->   LIMIT 8
    -> ) AS sc
    -> CROSS JOIN ( SELECT @rank := 0) AS param;
+------+---------+-------+
| rank | user_id | score |
+------+---------+-------+
|    1 |   13220 |    30 |
|    2 |   13222 |    30 |
|    3 |   16433 |    20 |
|    4 |   14621 |    15 |
|    5 |   14623 |    15 |
|    6 |   47899 |    10 |
|    7 |   47891 |    10 |
+------+---------+-------+
7 rows in set (0.00 sec)

MariaDB [yourschema]>