Mysql: Ordering by the order of values in a SQL IN() clause

I am wondering if there is away (possibly a better way) to order by the order of the 

values in an IN() clause.

The problem is that I have 2 queries, one that gets all of the IDs and the second that 

retrieves all the information. The first creates the order of the IDs which I want the 

second to order by. The IDs are put in an IN() clause in the correct order.

So it'd be something like (extremely simplified):

SELECT id FROM table1 WHERE ... ORDER BY display_order, name

SELECT name, description, ... WHERE id IN ([id's from first])

The issue is that the second query does not return the results in the same order that the

 IDs are put into the IN() clause.

One solution I have found is to put all of the IDs into a temp table with an auto 

incrementing field which is then joined into the second query.

Is there a better option?

Note: As the first query is run "by the user" and the second is run in a background 

process, there is no way to combine the 2 into 1 query using sub queries.

I am using MySQL, but I'm thinking it might be useful to have it noted what options 

there are for other DBs as well.

 Answers

Use MySQL's FIELD() function:

SELECT name, description, ...
FROM ...
WHERE id IN([ids, any order])
ORDER BY FIELD(id, [ids in order])

FIELD() will return the index of the first parameter that is equal to the first parameter 

(other than the first parameter itself).

FIELD('a', 'a', 'b', 'c')

will return 1

FIELD('a', 'c', 'b', 'a')

will return 3

This will do exactly what you want if you paste the ids into the IN() clause and the 

FIELD() function in the same order.

---

Two solutions that spring to mind:

1. order by case id when 123 then 1 when 456 then 2 else null end asc
2. order by instr(','||id||',',',123,456,') asc

(instr() is from Oracle; maybe you have locate() or charindex() 

or something like that)

---

The IN clause describes a set of values, and sets do not have order.

Your solution with a join and then ordering on the display_order column is the 

most nearly correct solution; anything else is probably a DBMS-specific hack 

(or is doing some stuff with the OLAP functions in standard SQL). Certainly, the join 

is the most nearly portable solution (though generating the data with the display_order

 values may be problematic). Note that you may need to select the ordering columns; 

that used to be a requirement in standard SQL, though I believe it was relaxed as a rule 

a while ago (maybe as long ago as SQL-92).

---

Using instr() function works. Here's slightly different solution

 that worked - SELECT id FROM table1 WHERE id IN (1, 20, 45, 60) 

ORDER BY instr('1, 20, 45, 60', id)

---

Use MySQL FIND_IN_SET function:

  SELECT * 
    FROM table_name 
   WHERE id IN (..,..,..,..) 
ORDER BY FIND_IN_SET (coloumn_name, .., .., ..);