Ferdousur Rahman Shajib Ferdousur Rahman Shajib - 1 year ago 65
SQL Question

MySQL ORDER BY multiple column ASC and DESC not working as expected

I have 1 mySQL table. Table entries are:

enter image description here

I want to show a list under 2 conditions. First the highest right_answers and also depending on lowest time_spent

I used this sql code to retrieve the list

mysql_query("SELECT * FROM `table_name` WHERE `contest_id` = '2' ORDER BY right_answers desc,time_spent")

I am getting this output:

enter image description here

My expected output shoulb be:

enter image description here

I tried many times with different queries but the result is still wrong. Could you give me some solutions?

Thanks in advance!


I guess your time_spent column has a VARCHAR(x) datatype, not a numeric datatype. So, MySQL is ordering the values in alphabetical order like this.


Your best repair of this problem is to change the datatype of that column to INT. (Also repair the right_answers column.)

ALTER TABLE table_name CHANGE COLUMN time_spent time_spent INT NOT NULL DEFAULT '0'
ALTER TABLE table_name CHANGE COLUMN right_answers right_answers INT NOT NULL DEFAULT '0'

Then your ORDER BY operation will work numerically and give you expected results.

Your second best repair is to coerce the data to numeric in your ORDER BY clause, like this:

 ORDER BY 0+right_answers DESC, 0+time_spent ASC