mysql custom sort

Solution 1:

SELECT * FROM table WHERE id IN (2,4,1,5,3) ORDER BY FIELD(id,2,4,1,5,3);

Source: http://imthi.com/blog/programming/mysql-order-by-field-custom-field-sorting.php

Solution 2:

i ask this :

mysql order by issue

the answers that i get and all the credit belong to them is :

You can use a CASE operator to specify the order:

SELECT * FROM table
WHERE id IN (3,6,1,8,9)
ORDER BY CASE id WHEN 3 THEN 1
                 WHEN 6 THEN 2
                 WHEN 1 THEN 3
                 WHEN 8 THEN 4
                 WHEN 9 THEN 5
         END

in php u can do it like :

<?php

$my_array =  array (3,6,1,8,9) ;

$sql = 'SELECT * FROM table  WHERE id IN (3,6,1,8,9)';

$sql .= "\nORDER BY CASE id\n";
foreach($my_array as $k => $v){
    $sql .= 'WHEN ' . $v . ' THEN ' . $k . "\n";
}
$sql .= 'END ';

echo $sql;

?>

Solution 3:

(I would have written this as a comment on Michel Tobon's answer, but don't have the reputation, sorry :-)

"And it worked... why? Beats me, but it just did; try it as well."

The reason that works is because your expression "code!='USA'" is producing a boolean result, which in SQL is represented as a 1 or 0. So, the expression "code='USA' produces a 1 for every record that matches that criterion, and a 0 for every record that does not. Because 1 is later than 0 in an ascending sort (the default), matching records will sort later than unmatching ones. Thus negating that expression producing the opposite effect.

Another (possibly clearer) way of producing the same result would be as follows:

SELECT * FROM countries ORDER BY code='USA' DESC, code='CAN' DESC, name ASC

Of course in answering the OP's question, I like the FIELD() option best - quite clean :-)