1

I have a table like this that keeps a total score of a history exam for students.

+----+---------------+-------+---------+-
| id | name          | history |  rank  | 
+----+---------------+-------+---------+-
|  1 | yngiid        |    97   |        | 
|  2 | viyrp         |   217   |        | 
|  3 | pae           |   599   |        | 
|  4 | spohl         |   284   |        | 
|  5 | shl           |   295   |        | 
|  6 | okeer         |    73   |        | 
|  7 | jmaany        |   657   |        | 
|  8 | hxt           |    80   |        | 
|  9 | yanier        |   599   |        | 
+----+---------------+-------+----------+-

The history field as you can see contains the total score the student . Now, I would like to rank the students by their scores as in, the higher the points the lower the rank.

Expected output.

+----+---------------+-------+----------+-
| id | name          | history |  rank  | 
+----+---------------+-------+----------+-
|  7 | jmaany        |   657   |   1    | 
|  3 | pae           |   599   |   2    | 
|  9 | yanier        |   599   |   2    | 
|  5 | shl           |   295   |   3    | 
|  4 | spohl         |   284   |   4    | 
|  2 | viyrp         |   217   |   5    |         
|  1 | yngiid        |    97   |   6    | 
|  8 | hxt           |    80   |   7    |     
|  6 | okeer         |    73   |   8    | 
+----+---------------+---------+--------+-

I have looked at answers in stackoverflow but the answers have some issues like:

In the below example, methods will increment the rank when users have the same score, as in the example of pae and yanier

|  7 | jmaany        |   657   |   1    | 
|  3 | pae           |   599   |   2    | 
|  9 | yanier        |   599   |   3    | # shold be 2 bc pae&yanier have equal points
|  5 | shl           |   295   |   4    | 

Other solution will fix the above problem but skip the the next rank number if scores are the same example:

|  7 | jmaany        |   657   |   1    | 
|  3 | pae           |   599   |   2    | 
|  9 | yanier        |   599   |   2    | 
|  5 | shl           |   295   |   4    | # should be 3 b/c .. logic

These are just some of the problems, so I am wandering if there is a solution to output the expected result.

Here is a sample table dump if it helps.

create table `test` (
    `id` int (11),
    `name` varchar (765),
    `history` int (11),
    `rank` int (11)
); 
insert into `test` (`id`, `name`, `history`, `rank`) values('1','yngiid','97',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('2','viyrp','217',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('3','pae','599',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('4','spohl','284',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('5','shl','295',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('6','okeer','73',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('7','jmaany','657',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('8','hxt','80',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('9','yanier','599',NULL);
1
SELECT
    T1.id,
    T1.`name`,
    T1.history,
    T2.rank
FROM
    test T1
LEFT JOIN (
    SELECT
        history,
        (@v_id := @v_Id + 1) AS rank
    FROM
        (
            SELECT DISTINCT
                history
            FROM
                test
            ORDER BY
                history DESC
        ) t,
        (SELECT @v_id := 0) r
) T2 ON T1.history = T2.history
ORDER BY
    history DESC

with result as:

7   jmaany  657 1
3   pae     599 2
9   yanier  599 2
5   shl     295 3
4   spohl   284 4
2   viyrp   217 5
1   yngiid  97  6
8   hxt     80  7
6   okeer   73  8

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.