CODEWITHSUNDEEP
phpmysql
Raz
Raz

Reputation: 9771

php mysql sort results by order fetched

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

Answers (5)

xception
xception

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

Joe G Joseph
Joe G Joseph

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

Raheel Hasan
Raheel Hasan

Reputation: 6033

simply add this at the end of your query:

order by field(id, 23, 33, 55, 2)

Upvotes: 6

Zeritor
Zeritor

Reputation: 313

SELECT id,name from table WHERE id IN (23, 33, 55, 2) ORDER BY FIELD (id, 2, 23, 33, 55)

Upvotes: 2

knittl
knittl

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

Related Questions