Reputation: 9771
How can I order the results I get by the order of the ids in the array when using IN statement:
SELECT id,name from table WHERE id IN (23, 33, 55, 2)
I want to get:
+----+---------+
| id | name |
+----+---------+
| 23 | name1 |
| 33 | name2 |
| 55 | name3 |
| 2 | name4 |
+----+---------+
in this order
Upvotes: 1
Views: 255
Reputation: 4297
SELECT id,name from table WHERE id IN (23, 33, 55, 2) ORDER BY id = 23 DESC, id = 33 DESC, id = 55 DESC, id = 2 DESC
not a database specific solution but still works
Upvotes: 1
Reputation: 24116
You could try something like this:
SELECT t.id,t.name
from
(select 23 as id,1 as rnk union all
select 33 as id,2 union all
select 55 as id,3 union all
select 2 as id,4 )input
join table t
on t.id=input.id
order by input.rnk
Upvotes: 1
Reputation: 6033
simply add this at the end of your query:
order by field(id, 23, 33, 55, 2)
Upvotes: 6
Reputation: 313
SELECT id,name from table WHERE id IN (23, 33, 55, 2) ORDER BY FIELD (id, 2, 23, 33, 55)
Upvotes: 2
Reputation: 265966
You can try to use a JOIN. It is not guaranteed to be returned in this order, but for MySQL this holds true most of the time:
SELECT table.id, table.name
FROM (
SELECT 23 id UNION SELECT 33 id UNION SELECT 55 id UNION SELECT 2
) a
JOIN table
ON a.id = table.id
How is your order determined? If you can provide a sorting-function it gets easy.
Upvotes: 2