CODEWITHSUNDEEP
phpmysqlsql-order-by
Ferdousur Rahman Shajib
Ferdousur Rahman Shajib

Reputation: 79

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!

Upvotes: 1

Views: 4303

Answers (4)

Sujin
Sujin

Reputation: 1

Try this

mysql_query("SELECT * FROM table_name WHERE contest_id = '2' and id in (SELECT ID FROM table_name ORDER BY right_answers DESC) ORDER BY time_spent ASC")

interchange column names and try again.

Upvotes: 0

O. Jones
O. Jones

Reputation: 108651

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.

 1
 10
 259
 46
 5
 6
 7894
 9

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

Upvotes: 3

Muhammad Shahzad
Muhammad Shahzad

Reputation: 9652

Try something like this:

SELECT *
FROM `table_name` WHERE `contest_id` = '2' 
ORDER BY SUBSTRING( time_spent
FROM 1 
FOR 1 ) ASC , right_answers DESC

Upvotes: 0

krishna ganisetti
krishna ganisetti

Reputation: 54

You try this way,I am trying its working

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

Upvotes: 0

Related Questions