With a SELECT...WHERE id IN (...), order results by IN()?

Question

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

With a query such as:

SELECT * FROM images WHERE id IN (12,9,15,3,1)

is it possible to order the results by the contents of the IN clause?

The result I'm looking for would be something like:

[0] => Array
    (
        [id] => 12
        [file_name] => foo
    )   
[1] => Array
    (
        [id] => 9
        [file_name] => bar
    )   
[2] => Array
    (
        [id] => 15
        [file_name] => baz
    )
...

This might be a nice question but it is a duplicate: http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause — Felix Kling, Mar 12 '10 at 21:32

score 10 · Accepted Answer · edited May 23 '17 at 12:34

10

The IN clause defines a set, and a set in mathematics has no order.

However there seems to be a workaround for MySQL by using the FIELD() function:

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

edited May 23 '17 at 12:34

Community

1
1

answered Mar 12 '10 at 21:28

Daniel Vassallo

337,827
72
505
443

2

Yes it is possible by tweaking the SQL query. See here: http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause – Felix Kling Mar 12 '10 at 21:34
@Felix: Thanks for the tip. Modified my answer to make it more accurate. – Daniel Vassallo Mar 12 '10 at 21:38

With a SELECT...WHERE id IN (...), order results by IN()?

1 Answers1

Linked