CODEWITHSUNDEEP
mysqlsql
user2179823
user2179823

Reputation: 7

mySQL Order by number query, not ordering by whole number

I have constructed a query here using mysql workbench:

USE wcms;
SELECT PLA_FNAME, PLA_LNAME, PLA_GS FROM player ORDER BY PLA_GS DESC;

It then displays these results:

enter image description here

As you can see, it is ordering the Player goals scored column by 9 being the highest value, not 20. What would be the best way to order this column by the whole number so 20 is at the top of the list?

Upvotes: 1

Views: 5040

Answers (5)

user2179823
user2179823

Reputation: 7

Thankyou everyone for your help. The issue was the column was the wrong datatype as previously stated in a comment. This was fixed by setting to column to an int:

ALTER TABLE PLAYER MODIFY COLUMN PLAY_GS INT(3);

Then running the query:

SELECT PLA_FNAME, PLA_LNAME, PLA_GS FROM player ORDER BY PLA_GS DESC;

Gave me the results correctly ordered.

Upvotes: 0

Nero
Nero

Reputation: 1285

This "sorting error"(1) happens, when using text data types for storing numbers. The command

DESCRIBE player;

shows, amongst other information, which data types are used for each column and will probably show some text data type like varcharin this case.

If you have access to changing the table structure, use a numerical data type for the numbers instead, like int. This will solve your sorting problem.

Additionally you'll save some space on your HDD and speed up your query. ints require far less memory than saving numbers in ascii format (which is what happens when using eg. varchar), and comparing integers is much faster than comparing (probably long) text elements.

If you don't have access to change the table structure, use the casting trick, already provided in the other answers.

(1) Not actually an error. Just character-wise sorting.

Upvotes: 0

KS Tech
KS Tech

Reputation: 194

Try this:

ORDER BY Cast(PLA_GS as int) DESC

Query:

USE wcms;
SELECT PLA_FNAME, PLA_LNAME, PLA_GS FROM player ORDER BY Cast(PLA_GS as int) DESC;

Upvotes: 1

mindandmedia
mindandmedia

Reputation: 6825

The cast is the appropriate solution, this could work for the lazy:

ORDER BY PLA_GS+0 DESC

Upvotes: 1

suspectus
suspectus

Reputation: 17288

Try this order statement

ORDER BY PLA_GS + 0 desc

Upvotes: 9

Related Questions