MySQL 'IN' clause and the returned record set order

Question

For example: select * from T where T.id IN(4,78,12,45)

I want the returned record set just order by the position in the 'IN' clause. How can I do this?

Answer 1

You could do it using FIND_IN_SET, e.g.

SELECT * FROM T WHERE T.id IN(4,78,12,45)
ORDER BY FIND_IN_SET(T.id,'4,78,12,45');

While you do have to duplicate the list, if you're generating the query in code this isn't a huge problem.

Answer 2

In the general case, you can't. SQL doesn't guarantee order unless you use the ORDER BY clause, and it can't be tied into the contents of an IN statement.

However, if you can build a temporary table that orders the values you're selecting from, you can join on that table and order by it.

For example, you have a temporary table that contains something like the following:

id  | order
----+------
4   | 1
78  | 2
12  | 3
45  | 4

Then you can order it like this:

SELECT T.*
FROM T
INNER JOIN temp
ON T.id = temp.id
ORDER BY temp.order ASC