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

Question

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.

Answer 1

Give this a shot:

SELECT name, description, ...
WHERE id IN
    (SELECT id FROM table1 WHERE...)
ORDER BY
    (SELECT display_order FROM table1 WHERE...),
    (SELECT name FROM table1 WHERE...)

The WHEREs will probably take a little tweaking to get the correlated subqueries working properly, but the basic principle should be sound.

Answer 2

Use mysql's FIELD() function:

SELECT name, description, ...
    FROM ...
    WHERE id IN ([ids from first])
    ORDER BY field(id, [ids from first])

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.

Answer 3

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)

Answer 4

My first thought was to write a single query, but you said that was not possible because one is run by the user and the other is run in the background. How are you storing the list of ids to pass from the user to the background process? Why not put them in a temporary table with a column to signify the order.

So how about this:

1. The user interface bit runs and inserts values into a new table you create. It would insert the id, position and some sort of job number identifier)
2. The job number is passed to the background process (instead of all the ids)
3. The background process does a select from the table in step 1 and you join in to get the other information that you require. It uses the job number in the WHERE clause and orders by the position column.
4. The background process, when finished, deletes from the table based on the job identifier.

Answer 5

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