Store the ranking table in mysql table

Question

Let this table ( rank_list ).

 +--------+--------+--------+--------+ | Sub | Name | Rank | Marks | +--------+--------+--------+--------+ |Math2 |Smith | 1| 85| |Math1 |John | 1| 92| |Math1 |Susan | 2| 91| |Math1 |Abc | 3| 90.5| +--------+--------+--------+--------+ 

Now I have four more results. I have to insert them, giving them the correct rank. I also do not accept the result that has a rank of more than 3.

 Sub = Math2, Name = Mile, Marks = 86 Sub = Math1, Name = Luna, Marks = 92.5 Sub = Physics1, Name = Sakib, Marks = 88.75 Sub = Math1, Name = Coolb, Marks = 65 

Since Mile has a character that takes 2, it will be inserted as Rank 2.

The moon has the highest ratings, it will receive Rank 1 and Abc will be deleted (yes, deleted).

Sakib is from physics1, so he will be inserted directly, since currently he takes 1st place in physics.

Coolb will not be inserted because it is not located at the top of 3.

I want a MySQL query to do this.

I can do this using PHP , but it will take a lot of time. So, I want to do this with SQL (for faster insertion and deletion). And I need your help. Thanks in advance.

Answer 1

Here is a snippet of a shamelessly stolen example from How to Perform Grouped Ranking in MySQL

The design of your database is not entirely suitable for it, but it works, and it will not be slow until you reach a very large number of rows, if you think it is too slow, you can always use a trigger and create a kind of “materialized view” "with current ranking.

Test script: http://sqlfiddle.com/#!2/b2cd8/15

SQL based on your table:

 SELECT Name, Sub, Marks, Rank FROM (SELECT Name, Sub, Marks, @student:=CASE WHEN @last_sub <> Sub THEN 1 ELSE @student+1 END AS Rank, @last_sub:=Sub FROM (SELECT @student:= 0) AS s, (SELECT @class:= 0) AS c, (SELECT * FROM rank_list ORDER BY Sub, Marks DESC ) AS temp ) AS temp2 WHERE Rank <= 3