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!
Add a comment
|
4 Answers
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
Comments
i ask this :
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;
?>
1 Comment
mkataja
Lucas Rossini Ferreira's solution is much simpler than this.
(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 :-)
1 Comment
opensourcejunkie
Thanks :-) (I have enough reputation to comment now! :-D)
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.
