MySQL not Sorting Data Correctly

Question

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?

This may be happening as the Score is being treated as a string instead of a number. — user2989408
– user2989408, Commented Jan 30, 2014 at 5:01
can you dump your table structure here?some time incorrect data type gone crazy while sorting. — Elshan
– Elshan, Commented Jan 30, 2014 at 5:02
Try to cast your score before sorting in SQL and see what happens. — user2989408
– user2989408, Commented Jan 30, 2014 at 5:03

nl-x · Accepted Answer · 2014-01-30 05:03:03Z

4

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.

answered Jan 30, 2014 at 5:03

nl-x

11.9k7 gold badges36 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2989408 · Accepted Answer · 2014-01-30 05:07:26Z

0

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

answered Jan 30, 2014 at 5:07

user2989408

3,1371 gold badge20 silver badges15 bronze badges

Collectives™ on Stack Overflow

MySQL not Sorting Data Correctly

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related