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

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

Answer 2

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 3

You cannot order by the IN clause. You must provide a separate ORDER BY clause. A way of doing this is as follows by building up a case statement in the ORDER BY. It's not pretty though.

SELECT
 *
FROM
 T
WHERE
 T.Id IN(4, 78, 12, 45)
ORDER BY
 CASE
  WHEN T.Id = 4 THEN 1
  WHEN T.Id = 78 THEN 2
  WHEN T.Id = 12 THEN 3
  WHEN T.Id = 45 THEN 4
 END