CODEWITHSUNDEEP
phpmysql
ChileCaliente
ChileCaliente

Reputation: 31

Order from MySQL order

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?

Upvotes: 2

Views: 57

Answers (3)

spencer7593
spencer7593

Reputation: 108530

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.

Upvotes: 2

harrrrrrry
harrrrrrry

Reputation: 14507

Convert it into int

ORDER BY CAST(ranks.rank_number AS Numeric(10,0)) ASC

From Sql Server query varchar data sort like int

Upvotes: 1

Zak
Zak

Reputation: 25237

Basedon your results it looks like you are getting alphabetic sorting. it looks like in your database, the rank_number is likely stored as "varchar" instead of integer. in that case, you can cast the varchar to an integer to get numeric sorting instead of alphabetic sorting.

https://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

SELECT users.*, CAST (ranks.rank_number AS SIGNED) as user_rank 
...
order by user_rank ASC

Posting your table definitions will help clarify if this is actually the case..

Upvotes: 2

Related Questions