CODEWITHSUNDEEP
mysql
yoyo
yoyo

Reputation: 1133

How to make MySQL result set the same as specified?

Select * from table where id in ($id_list)...

How to make the result the same order as specified in $id_list ?

If $id_list='2,1,5...' then the result set should be in the same order

Upvotes: 2

Views: 152

Answers (3)

The Scrum Meister
The Scrum Meister

Reputation: 30111

Use the FIELD() function.

SELECT * 
FROM table
WHERE id IN ($id_list)
ORDER BY FIELD(id, $id_list)

Using FIELD() is better then using FIND_IN_SET(), Since they are compared as numbers.

Upvotes: 1

Naveed
Naveed

Reputation: 42093

Try FIND_IN_SET

SELECT * FROM tablename 
WHERE id IN (1,5,10) 
ORDER BY FIND_IN_SET(id, '1,5,10')

Upvotes: 2

Haim Evgi
Haim Evgi

Reputation: 125476

you need to order by with switch case

SELECT * FROM table
WHERE id IN (3,6,1,8,9)
ORDER BY CASE id WHEN 3 THEN 1
                 WHEN 6 THEN 2
                 WHEN 1 THEN 3
                 WHEN 8 THEN 4
                 WHEN 9 THEN 5
         END

read more details on

MySQL ORDER problem

Upvotes: 1

Related Questions