Assign a value to a row from the position of a sort table

Question

I have a MySQL table with rows: id, score and rank. I want to make a trigger that sort the table and assign the new position to the rank value of the table, for example: id. nick. score. rank

John. 8. ?
Mike. 10. ?
Al. 5. ?
Terry. 12. ? SORT BY score= id nick. score. rank.
Terry. 12. 1
Mike 10. 2
John. 8. 3
Al. 5. 4

I know how to sort the table with the sentence ORDER BY score DESC, but I don't know how to assign the new position of the elements to the rank. Thanks.

Answer 1

I would not recommend storing the rank in the table itself. This is a derived information, that can be computed on the fly whenever needed. On the other hand, storing it incurs maintaining it: you will find yourself creating stored procedures for each and every DML operation (update, insert, delete).

You could create a view instead. If you are running MySQL 8.0 or MariaDB >= 10.3, you can use window functions:

create view myview as
select 
    id, 
    score,
    rank() over(order by score desc) rnk
from mytable 

This gives you an always up-to-date perspective at your data, with 0 maintenance costs.

Note that rank is a SQL keyword, hence not a good choice for a column name - I named the column rnk instead.