mysqlsqlsql-order-by

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.

Solution

  • 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.