Thesql logical ranking based on score

MySQLmysql-5.6rank

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);

Best Answer

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

Related Solutions

Mysql – Drop auto increment hack w/o alter table

I tried something similar just now

Here is MySQL for My PC

mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION                 | 5.5.12-log                   |
| VERSION_COMPILE_MACHINE | x86                          |
| DATADIR                 | C:\MySQL_5.5.12\data\        |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

I will run this using MyISAM

Step 01) create a table called 'rolando'
Step 02) insert 'dominique' and 'diamond'
Step 03) copy the table structure to 'pamela'
Step 04) alter 'pamela' to not have auto_increment
Step 05) In DOS, copy rolando.MYD to pamela.MYD
Step 06) run REPAIR TABLE pamela; (Rebuild pamela.MYI)
Step 07) run SELECT COUNT(1) FROM pamela;
Step 08) run SHOW CREATE TABLE pamela\G
Step 09) run SELECT * FROM pamela;
Step 10) insert 'carlik' into pamela
Step 11) run SELECT * FROM pamela;

Let's see if these steps are kosher.

Here are Steps 1-4

mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)

mysql> create table rolando
    -> (
    ->     name varchar(20),
    ->     id int not null auto_increment,
    ->     primary key (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from rolando;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
+-----------+----+
2 rows in set (0.00 sec)

mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table rolando\G
*************************** 1. row ***************************
       Table: rolando
Create Table: CREATE TABLE `rolando` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

Here is Step 6

C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
        1 file(s) copied.

C:\>

Here are the rest of the Steps starting at Step 7

mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning  | Number of rows changed from 0 to 2 |
| test.pamela | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pamela;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
| carlik    |  3 |
+-----------+----+
3 rows in set (0.00 sec)

mysql>

Dangerous game, isn't it ???

Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

The technique we are about to demonstrate is unsupported, undocumented, and may not work. Use it at your risk. We advise you to back up you data first!

I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?

You got guts, @atxdba !!!

Mysql – score ranking no tie, if tie order by score submit date

Order by Score in descending order, and by SubmitDate in ascending order, then use a variable to give correlative numbers to each row:

mysql> SET @Rank := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT *, @Rank := @Rank + 1 AS Rank FROM ranking ORDER BY Score DESC, SubmitDate ASC;
+----+-------+-------+---------------------+------+
| Id | Name  | Score | SubmitDate          | Rank |
+----+-------+-------+---------------------+------+
|  4 | Bash  |   102 | 2014-08-04 21:37:17 |    1 |
|  1 | Ida   |   100 | 2014-08-01 21:37:17 |    2 |
|  2 | Boo   |    99 | 2014-08-02 21:37:17 |    3 |
|  3 | Lala  |    99 | 2014-08-03 21:37:17 |    4 |
|  5 | Assem |    99 | 2014-08-05 21:37:17 |    5 |
+----+-------+-------+---------------------+------+
5 rows in set (0.00 sec)

Expected output.

Best Answer

Related Solutions

Mysql – Drop auto increment hack w/o alter table

Mysql – score ranking no tie, if tie order by score submit date

Related Question