CODEWITHSUNDEEP
mysql
user614382
user614382

Reputation: 141

SQL order by sequence of IN values in query

For example I have this code:

SELECT *
FROM table_name
WHERE column_name IN (value6,value4,value11,value19)

Is there a way to order the result in that exact sequence in the query? The code above returns the default ordering of table_name, completely disregarding the sequence of IN values. I know I can do additional coding in PHP to properly order them according to the IN values but that would create a significant overhead.

Upvotes: 14

Views: 9657

Answers (2)

gamov
gamov

Reputation: 3859

For a DB agnostic answer, look here: Order resultset based on WHERE IN clause data However, I would love to have an array construct that is DB agnostic.

Upvotes: 0

Mchl
Mchl

Reputation: 62377

SELECT 
  * 
FROM 
  table_name 
WHERE column_name IN (value6,value4,value11,value19) 
ORDER BY FIELD(column_name,value6,value4,value11,value19)

Explanation here: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_field

  • FIELD(str,str1,str2,str3,...)

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

Upvotes: 25

Related Questions