CODEWITHSUNDEEP
phpmysqlsqlsql-order-by
three3
three3

Reputation: 2846

MySQL not Sorting Data Correctly

I have been using MySQL for a long time and I have never run across this issue. I have a table that stores the scores for an application. For some reason, when I sort by score ASC, the highest score is shown first with the lowest score being last. Please see the screenshot below:

enter image description here

Here is my query: 

SELECT category, subject, max(score) as score FROM scores 
WHERE customer_id = 1086 AND category = 'Business' 
GROUP BY subject ORDER BY score ASC

Any thoughts on why this is happening?

Upvotes: 1

Views: 1671

Answers (2)

user2989408
user2989408

Reputation: 3137

Try selecting CAST(max(score) AS INTEGER) as Score and then ORDER BY Score.

Upvotes: 0

nl-x
nl-x

Reputation: 11832

Change the datatype of score from string (eg varchar/text) to a number (eg int). That should solve the sorting issue.

When the values are sorted on string basis (alphabetically), the '6' in '60' comes before '8'.

As a temporary work-around you can also try order by score+0 asc to try and convert your value into a number.

Upvotes: 4

Related Questions