userxöa userxöa - 1 month ago 11
MySQL Question

Sort values in two column and insert order position into another column in mysql

I have a database about sports event that contains:

*User ID

*Amount of Points that the user got on that event

*Time (HH:MM:SS) that took the user to complete track.


How can I first sort them by no. of points, then if two users have same amount of points, by time (shorter is better); and then insert the places to rows?

I have database like that:


ID No. of Points Time Place
------------------------------------
1 15 00:56:00
2 13 00:55:15
3 17 01:00:00
4 17 00:57:00
5 19 00:52:15


I need to have it with places:


ID No. of Points Time Place
------------------------------------
1 15 00:56:00 4
2 13 00:55:15 5
3 17 01:00:00 3
4 17 00:57:00 2
5 19 00:52:15 1


I hope, you understand that. Sorry for bad English.

Best regards,

Answer
You can do this with update statement as follows. 

SET @placeValue:=0;

UPDATE [Table Name] SET Place=@placeValue:=@placeValue+1 ORDER BY 
[Amount of Points] DESC,Time ASC
Comments