CODEWITHSUNDEEP
mysql
rockstardev
rockstardev

Reputation: 13537

MYSQL: Order by search query IN function?

I have a query:

SELECT * FROM products WHERE id IN (1,2,3)

This returns:

ID | Title
1  | Bla
2  | Bla2
3  | Bla3

If I change the query to:

SELECT * FROM products WHERE id IN (2,3,1)

I still get: 

ID | Title
1  | Bla
2  | Bla2
3  | Bla3

How can I change my query to get:

ID | Title
2  | Bla2
3  | Bla3
1  | Bla

For the second query? In other words, order the result by the order of the items in the IN query? Is it possible to say something like:

ORDER BY ID (2,3,1)

?

Upvotes: 0

Views: 46

Answers (3)

Taryn
Taryn

Reputation: 247850

You can use a CASE expression in your ORDER BY clause:

SELECT * 
FROM products 
WHERE id IN (2,3,1)
order by 
  case id 
    when 2 then 0
    when 3 then 1
    when 1 then 2
    else 3
  end

See SQL Fiddle with Demo

Upvotes: 3

fthiella
fthiella

Reputation: 49089

You could use something like this:

SELECT *
FROM products
WHERE id IN (2,3,1)
ORDER BY FIND_IN_SET(id, '2,3,1')

Or you can also order by:

ORDER BY FIELD(id, 3,2,1)

Please see fiddle here.

Upvotes: 3

jcho360
jcho360

Reputation: 3759

you have to use ORDER BY <ASC/DESC> 

SELECT * FROM products WHERE id IN (1,2,3) order by id desc

Upvotes: 0

Related Questions