0

I have a table of exam result. as follows.

ID    | Score    | Rank
-------------------------
 5    |  34      | 2
 13   |  47      | 1

and so on.

I need to compute the rank of a particular ID and save it at the time of insertion of the row to the table. At the same time, the corresponding changes to other rows must also reflect.

So after inserting a new row it should be like

ID    | Score    | Rank
-------------------------
 5    |  34      | 3
 13   |  47      | 1
 15   |  39      | 2

We can calculate the rank using RANK() function of SQL.

But I can't update it to the same table.

Thanks for any help, in advance.

Jishad
  • 151
  • 4
  • 14
  • 4
    This is an expensive operation. I would suggest doing the calculation when you query the table rather than when you insert. – Gordon Linoff Feb 04 '18 at 13:15
  • Possible duplicate of [Rank function in MySQL](https://stackoverflow.com/questions/3333665/rank-function-in-mysql) – Raymond Nijland Feb 04 '18 at 13:15
  • `using the RANK() function of SQL` ... MySQL doesn't support `RANK`, not unless you are using a very new version of it, which most people aren't using. It is a pain to calculate the rank in MySQL for this reason. – Tim Biegeleisen Feb 04 '18 at 13:16
  • MySQL 8.0+ which supports RANK and other window functions isn't ready for production. So you need to simulate with user variables or use a correlated subquery to get the ranking. Check mine last comment for those options.. – Raymond Nijland Feb 04 '18 at 13:20

0 Answers0