Get result from mysql orderd by IN clause

Question

I have the following query

SELECT * FROM invoice WHERE invoice_id IN (13, 15, 9, 27)

My result is:

invoice_id | invoice_number | ...
------------------------------------
9          | 201006003      |
13         | 201006020      |
15         | 201006022      |
27         | 201006035      |

which is the result set I want except that is ordered by the invoice_id (which is an autoincrement value).

Now I want the result in the order I specified in my query (13, 15, ...). Is there a way to achive that?

The background is that I have a DataTable bound to a DataGridView. The user can filter and sort the result but if he want's to print the result I don't use the DataTable for printing because it only contains the most important columns and instead I pull the whole records from the database and pass it to my printing control.

I also tried to extend the existing DataTable with the missing results but that seems to slower than using the IN (...) query.

Answer 1

It's ugly, but you could do:

ORDER BY CASE invoice_id WHEN 13 THEN 0 WHEN 15 THEN 1 WHEN 9 THEN 2 WHEN 27 THEN 3 ELSE 4 END

Actually, there's the FIELD function:

ORDER BY FIELD(invoice_id, 13, 15, 9, 27)

The FIELD function returns the position of the first argument in the list of the rest.

Or, if you're generating it dynamically, you could do:

WHERE invoice_id IN ({list}) ORDER BY FIND_IN_SET(invoice_id, '{list}')

Answer 2

You want the FIELD order by parameter.

SELECT * FROM invoice WHERE invoice_id IN (13, 15, 9, 27) ORDER BY FIELD (invoice_id, 13, 15, 9, 27)