CODEWITHSUNDEEP
mysqlsorting
Patrick
Patrick

Reputation: 949

mysql custom sort

I have a query like this: SELECT * FROM table WHERE id IN (2,4,1,5,3);

However, when I print it out, it's automatically sorted 1,2,3,4,5. How can we maintain the order (2,4,1,5,3) without changing the database structure?

Thanks!

Upvotes: 22

Views: 16754

Answers (4)

opensourcejunkie
opensourcejunkie

Reputation: 543

(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 :-)

Upvotes: 6

Michel Tobon
Michel Tobon

Reputation: 21

The Ordering by field never worked for me. I had a list of countries and I needed United States and Canada to appear on top of the list, so my query was like this:

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

This never worked, but I realized that the ordering was different, it was showing Canada and the States at the end of the list, so I did this:

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

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

Upvotes: 2

Lucas Rossini
Lucas Rossini

Reputation: 602

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

Upvotes: 59

Haim Evgi
Haim Evgi

Reputation: 125614

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;

?>

Upvotes: 21

Related Questions