0

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?

1
  • For starters, upgrade to the current version of MySQL 8 - lots of new goodies which will make your life much easier - including Window functions (RANK inter alia). I'm not even going to try and answer till you've done that. Secondly, could you please include your table structures as DDL (CREATE TABLE blah (...);) and your table data as DDL (INSERT INTO TABLE blah VALUES (...);) - makes life much easier for those trying to assist you. You can do this by editing your question! Help us to help you! p.s. welcome to the forum! :-)
    – Vérace
    Jun 5, 2019 at 11:49

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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