Mysql – generating trends of rank

MySQLmysql-5.7

I've a table of students score

      ID_STUDENT | SCORE
     ------------------
           1     |  90
           2     |  80
           3     |  99
           4     |  80
           5     |  70
           6     |  78
           7     |  90
           8     |  50
           9     |  90

So lets say on first day I'll compute the rank and store it in one column say RANK – 

      ID_STUDENT | SCORE  | RANK
     ----------------------------
           3     |  99    |  1
           1     |  90    |  2
           7     |  90    |  2
           9     |  90    |  2
           2     |  80    |  3
           4     |  80    |  3
           6     |  78    |  4
           5     |  70    |  5
           8     |  50    |  6

on second day I'll refresh the score and will recompute the rank. However, here is the thing, I need the records of prompt past rank as well, like this –

      ID_STUDENT | SCORE  | RANK | OLD_RANK
     ---------------------------------------
           2     |  99    |  1   |  3
           8     |  92    |  2   |  6
           1     |  90    |  3   |  2
           9     |  90    |  3   |  2
           3     |  80    |  4   |  1
           4     |  80    |  4   |  3
           6     |  78    |  5   |  4
           5     |  70    |  5   |  5
           7     |  40    |  6   |  2

So using this outcome, I would able find the rank trend,much the same as you can see the songs position going up/down over a week as shown here – https://www.billboard.com/charts/hot-100

How can I achieve this using straightforward DML queries?

Best Answer

In MySQL 8.0, there is a "straightforward" way using the windowing functions of RANK or DENSE_RANK.

If 5.7, there is no practical SQL way. It would be better to pull the data into your app to compute the ranks.

I have a quibble with 1-2-2-2-3-... -- The guy with rank 3 is really 5th in the list. The 3-guy will be disappointed when he drops to 4 or 5 tomorrow when the 2-guys' scores change a little.

Related Question