Embedded select with rank

Question

I am having the hardest time following the tutorials on this site regarding ranking with MySQL. I have a larger problem than others seem to show. My table is constantly updating with single answers (ans) corresponding to a matching word (word). I also have to check whether the row has an answer at all (ans IS NOT NULL).

I've devised the following:

SELECT userid, floor(sum(ans = word) / count(id) * 100) as score
FROM fb_asl.quiz
WHERE DATE(`time`) BETWEEN CURDATE() + INTERVAL (-3) DAY AND CURDATE() + INTERVAL (-3) DAY
AND ans IS NOT NULL
GROUP BY userid
ORDER BY score DESC;

This returns the following

1504    93
3567    79
8225    66

How can I go about getting a rank corresponding to a specific userid? Say returning something like:

2   3567    79

Answer 1

Based on the above query, I have a solution for you

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT score,userid,rank FROM
(
SELECT AA.*,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
FROM
(
    SELECT
        userid,
        floor(sum(ans = word) / count(id) * 100) as score
    FROM fb_asl.quiz
    WHERE
        DATE(`time`) BETWEEN
        CURDATE() + INTERVAL (-3) DAY AND
        AND CURDATE() + INTERVAL (-3) DAY
        AND ans IS NOT NULL
    GROUP BY userid
) AA ORDER BY score DESC) A;

Let me demonstrate it with some sample data

SAMPLE DATA

use test
DROP TABLE IF EXISTS scores;
CREATE TABLE scores
(
    id int not null auto_increment,
    score int not null,
    primary key (id),
    key score (score)
);
INSERT INTO scores (score) VALUES
(50),(40),(75),(80),(55),
(40),(30),(80),(70),(45),
(40),(30),(65),(70),(45),
(55),(45),(83),(85),(60);

SAMPLE DATA LOADED

mysql> DROP TABLE IF EXISTS scores;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE scores
    -> (
    ->     id int not null auto_increment,
    ->     score int not null,
    ->     primary key (id),
    ->     key score (score)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO scores (score) VALUES
    -> (50),(40),(75),(80),(55),
    -> (40),(30),(80),(70),(45),
    -> (40),(30),(65),(70),(45),
    -> (55),(45),(83),(85),(60);
Query OK, 20 rows affected (0.02 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql>

WHAT SAMPLE DATA LOOKS LIKE

mysql> SELECT * FROM scores;
+----+-------+
| id | score |
+----+-------+
|  1 |    50 |
|  2 |    40 |
|  3 |    75 |
|  4 |    80 |
|  5 |    55 |
|  6 |    40 |
|  7 |    30 |
|  8 |    80 |
|  9 |    70 |
| 10 |    45 |
| 11 |    40 |
| 12 |    30 |
| 13 |    65 |
| 14 |    70 |
| 15 |    45 |
| 16 |    55 |
| 17 |    45 |
| 18 |    83 |
| 19 |    85 |
| 20 |    60 |
+----+-------+
20 rows in set (0.00 sec)

mysql>

MY SOLUTION AGAINST THE SAMPLE DATA

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 scores GROUP BY score
    ) AAA
    ORDER BY score DESC
) AA LEFT JOIN scores BB USING (score)) A;

MY SOLUTION EXECUTED AGAINST THE SAMPLE DATA

mysql> 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 scores GROUP BY score
    ->     ) AAA
    ->     ORDER BY score DESC
    -> ) AA LEFT JOIN scores BB USING (score)) A;
+-------+------+------+
| score | ID   | rank |
+-------+------+------+
|    85 |   19 |    1 |
|    83 |   18 |    2 |
|    80 |    4 |    3 |
|    80 |    8 |    3 |
|    75 |    3 |    5 |
|    70 |    9 |    6 |
|    70 |   14 |    6 |
|    65 |   13 |    8 |
|    60 |   20 |    9 |
|    55 |    5 |   10 |
|    55 |   16 |   10 |
|    50 |    1 |   12 |
|    45 |   10 |   13 |
|    45 |   15 |   13 |
|    45 |   17 |   13 |
|    40 |    2 |   16 |
|    40 |    6 |   16 |
|    40 |   11 |   16 |
|    30 |    7 |   19 |
|    30 |   12 |   19 |
+-------+------+------+
20 rows in set (0.00 sec)

Give it a Try !!!