Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do you keep the order using SELECT WHERE IN()?

Tags:

Is there a way to keep the order when using SELECT WHERE IN()? For example, using the following query:

SELECT id FROM data_table WHERE id IN(56,55,54,1,7); 

The results will come back using the default order by id. 1,7,54,55,56

When I want to keep the order used in the IN: 56,55,54,1,7

Is there a quick way to do this in MySQL or will I be forced to order it after in code?

like image 486
Pepper Avatar asked May 11 '10 19:05

Pepper


People also ask

Can we use WHERE clause with ORDER BY?

You can use the WHERE clause with or without the ORDER BY statement. You can filter records by finite values, comparison values or with sub-SELECT statements.

How do you use WHERE in SELECT?

WHERE clause Syntax. The basic syntax for the WHERE clause when used in a MySQL SELECT WHERE statement is as follows. “WHERE” is the keyword that restricts our select query result set and “condition” is the filter to be applied on the results. The filter could be a range, single value or sub query.

Does ORDER BY go after WHERE?

The ORDER BY clause is used to get the sorted records on one or more columns in ascending or descending order. The ORDER BY clause must come after the WHERE, GROUP BY, and HAVING clause if present in the query.

Does ORDER BY go before WHERE?

ORDER BY is always put at the very end of the query.Clauses like FROM , WHERE , GROUP BY , HAVING , etc. should be put before the ORDER BY keyword. To sort the output in ascending order, you may put the keyword ASC after the column name.


2 Answers

Use FIND_IN_SET:

ORDER BY FIND_IN_SET(id, '56,55,54,1,7') 
like image 198
OMG Ponies Avatar answered Oct 27 '22 22:10

OMG Ponies


You can also use FIELD:

ORDER BY FIELD(id, '56,55,54,1,7') 
  • http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field
  • http://ivanjovanovic.com/2008/04/01/preserving-ordering-with-where-in-clause-in-mysql/
like image 21
RafaSashi Avatar answered Oct 27 '22 23:10

RafaSashi