CODEWITHSUNDEEP
mysql
panthro
panthro

Reputation: 24061

Using IN and ordering?

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:

id
100
300
400

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

id
300
400
100

Upvotes: 5

Views: 47

Answers (2)

Linger
Linger

Reputation: 15058

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.

Upvotes: 0

evilone
evilone

Reputation: 22740

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

Upvotes: 12

Related Questions