Menu
I'm selecting a set of account records from a large table (millions of rows) with integer id values. As basic of a query as one gets, in a sense. What I'm doing us building a large comma separated list, and passing that into the query as an "in" clause. Right now the result is completely unordered. What I'd like to do is get the results back in the order of the values in the "in" clause.
I assume instead I'll have to build a temporary table and do a join instead, which I'd like to avoid, but may not be able to.
Thoughts? The size of the query right now is capped at about 60k each, as we're trying to limit the output size, but it could be arbitrarily large, which might rule out an "in" query anyway from a practical standpoint, if not a physical one.
Thanks in advance.
955
The MySQL ORDER BY clause is used to sort the records in your result set.
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 .
Answer: ORDER BY is a clause that is typically used along with SELECT queries in MySQL and is used to return the result set sorted in the given order against the given column or field in the table.
The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
Actually, this is better:
SELECT * FROM your_table WHERE id IN (5,2,6,8,12,1) ORDER BY FIELD(id,5,2,6,8,12,1); heres the FIELD documentation:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field
93
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
