ORDER BY orders by the first house and not by the number itself

Question

I’m pulling values with order by and they’re not right in either ASC or DESC.

My code is this, how can I fix this mistake?

$sql = 'SELECT *, Player.ID AS ID, LEFT(FirstName,1) AS FirstName, FirstName AS FName FROM Player INNER JOIN OverallRatings ON OverallRatings.PID = Player.ID INNER JOIN franchises ON franchises.GameID = Player.CurrentTeamID '.$where.' ORDER BY '.$sort.' '.$by.' LIMIT 250';

Print of the order.

Answer 1

Hey, Maycon. What’s up? You need to change the type of field in your table to int. In the example you put is as text (char, varchar, text).

When you use text field sorting it will consider alphabetical sorting. With this, when you have 1,12,11,9 for the ordination would be 1,11,12,9. For he began first that he was with 1 and so on.

In this ordering, common numbers and special characters always precede the letters in the matter of precedence, as you can see in the ASCII or UNICODE table.

This causes a counterintuitive effect when we are dealing only with numbers, as is your case, because 489 will always appear before 75 (because 4 (U+0034) is less than 7 (U+0037)), for example, and so on.

Answer 2

As @Leandro commented, if you want to sort numerically, it would be better to change the field type to numeric (integer for example).

An alternative solution, as long as the table is not too large, is to convert the field to numeric at the time of sorting, using CONVERT() for example:

$sort = convert(nome-do-campo, signed integer)

See here an example working: Sqlfiddle