Priya Priya - 1 year ago 82
MySQL Question

Multiple Column Order MySQL

I am trying to order by code in one of my quiz application api. I want result sort by score and time of user. I have column like score and time. score contain user's score and time contains time user have took for finish quiz. I want sort them in result as highest score + lowest time will be first. current my code for it is like below

SELECT * FROM contest_score order by score desc, time ASC

This code working but if there multiple user have same score then its showing only one user which have lowest time. What should I correct for get result ?

For example if there 3 user have score and time like below

user1 : score= 100, time =5.0
user2 : score = 98 time = 6.0
user3 : score = 100 time = 4.5

I want arrange user like first= user3, second=user1 and third user2


Answer Source

I mean you could do this:

FROM contest_score 
order by 
   score desc, 
   CAST(time as DECIMAL(9,2)) ASC

It is a bit dirty. The better option would be to save it as decimal instead

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download