Using IN and ordering?

Question

Here's my query:

SELECT * FROM article WHERE id IN (300, 400, 100)

But it always orders the articles the same, by id.

So I would get out for the above:

Is there a way to get the results out in the order that they were requested in the IN statement? eg.

Unless you provide an `ORDER BY` clause, result *sets* from an SQL select are by definition unordered — knittl, Jul 24 '14 at 13:29
Duplicate: http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause?rq=1 — Matt, Jul 24 '14 at 13:31

evilone · Answer 1 · 2014-07-24T13:29:09.443

12

You can try something like this. Use the FIELD() function.

SELECT * FROM article 
WHERE id IN (300, 400, 100) 
ORDER BY FIELD(id, 300, 400, 100);

edited Jul 24 '14 at 13:29

answered Jul 24 '14 at 13:28

evilone

score 0 · Answer 2 · answered Jul 24 '14 at 13:34

You could drop the values you want into a temporary table like so:

CREATE TABLE TempT (sortOrder int,
                    id int);

INSERT INTO TempT (sortOrder, id) VALUES
(1, 300),(2, 400),(3, 100);

And then just join to the table like so:

SELECT a.*
FROM article a
INNER JOIN TempT t ON a.id = t.id
ORDER BY t.sortOrder;

SQL Fiddle example. This works well if you have a lot of values you need to incorporate in the IN statement.

2 Answers2