从MySQL订单订购

Question

This is MySQL query:

$sql = "SELECT users.*, ranks.rank_number
        FROM users
        INNER JOIN ranks
        ON users.rank_name=ranks.rank_name
        WHERE users.active= 'y'
        ORDER BY ranks.rank_number ASC, users.plvl DESC
        LIMIT $start, $limit";
$result = mysql_query($sql);

I want to order by rank_number as:

1,2,3,4,5,6,7,8,9,10,11,12

from MySQL, but I get this:

1,11,12,10,2,3,4,5,6,7,8,9

So how can I make it as 1,2,3,4,5,6,7,8,9,10,11,12?

Answer 1

The quickest fix to your statement is to append +0 to ranks.rank_number in the ORDER BY clause...

ORDER BY ranks.rank_number+0 ASC, ...
                          ^^

That will "work" if rank_number is CHAR or VARCHAR datatype, and you want to sort by the numeric value, rather than sorting the string representation.

There are other expressions that achieve an equivalent result, converting the string representation into a numeric value. The "add zero" trick is the shortest fix, just a two character addition to the statement.

Normally, we tend to store numeric values in columns declared with numeric datatypes (integer, decimal, float), rather than storing them as strings. Then we avoid the kind of issue you are reporting with ordering.

---

The order you show the rows being returned in is very odd.

Are you positive that 10 is being ordered after 12? If this was being sorted/ordered as string values, we'd expect the string value 10 to come before 11. The order that you report the rows being returned in is just very odd.

(If the order you report the rows being returned in is accurate, then I suspect that second character is actually capital O 'O' rather than a zero '0'.

If that's the case, then the +0 trick will convert '1O'+0 (one and capital O) into a numeric value of 1, so that would be equivalent to '1'+0, which will also evaluates to 1.