0

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
  • How should the case of 93, 79, 79, 66 be ranked? Should it be a) 1, 2, 3, 4, b) 1, 2, 2, 4, or c) 1, 2, 2, 3? If a), how should the query determine which userid must be assigned which ranking in case of ties? – Andriy M Jun 3 '13 at 11:57
  • And by the way, you could simplify the score expression a little: floor(avg(100 * (ans = word))) as score. – Andriy M Jun 3 '13 at 12:02
  • Andriy M, that's a good point. I'm indifferent between a) 1, 2, 3, 4, b) 1, 2, 2, 4. I guess in the case of a) I'll let the userid sort ASC. Also thanks for the tip on avg(). I completely forgot about that function. – SmujMaiku Jun 3 '13 at 13:48
  • There are many suggestions in this thread. – Andriy M Jun 3 '13 at 15:02
3

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 !!!

  • Maybe there's no need to write another answer. There've been quite a few already, yours included. Since you've got enough reputation, you could just suggest closing this question as a duplicate. – Andriy M Jun 3 '13 at 15:06
  • @AndriyM I am going to delete my old answer because nobody has upvoted it and my answer handles ties and consecutive ranking differently from the others. Thus, I am migrating that answer away from that old question. – RolandoMySQLDBA Jun 3 '13 at 15:11
  • 2
    @RolandoMySQLDBA why do you always think that because an answer hasn't been up-voted it should be deleted? Answers can still be useful to people - especially yours, since they're so detailed - including to people who come here anonymously or don't yet have enough reputation to vote. – Aaron Bertrand Jun 3 '13 at 15:22
  • @AaronBertrand I think I'm kind of influenced by two posts in Meta StackOverflow: 1) meta.stackexchange.com/q/108150 , 2) meta.stackexchange.com/q/47584. Even though these two posts discuss retiring questions, I was having mixed feelings about my answers in this respect. Actually, I wasn't sure if my answers were helpful to anyone. – RolandoMySQLDBA Jun 3 '13 at 16:30
  • @RolandoMySQLDBA I'd assume they're helpful (or may be helpful in the future) unless you have concrete evidence of the opposite. You can't measure helpfulness by solely looking at up-votes. – Aaron Bertrand Jun 3 '13 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.