MySQL WHERE IN results are out of order

I'm using this query:

SELECT * FROM `challenges` WHERE id IN (#, #, #, #)

Where the #'s are 4 ID numbers associated with a single row. This works well since it allows me to pull out 4 rows from the table in one query, but there's an odd issue with it. I don't know why it does it like this, but the order of the rows in the result isn't the same as the order I have them in in the query. For example:

SELECT * FROM `challenges` WHERE id IN (108, 208, 134, 142)

I want them to come out in that order, but they instead come out like this:

108, 134, 142, 208

The only thing I notice is that each row in the challenges table has a field called "first_recorded", a开发者_如何学JAVAnd the order of the rows coming out of this query is sorted by that field, ascending. Is there any way to force the query to order the results in the same order as the IDs are ordered?

You need to order by field

SELECT * FROM `challenges` WHERE id IN (108, 208, 134, 142) ORDER BY FIELD(id, 108,208,134,142)

you can use the mysql order by field:

SELECT * FROM `challenges` WHERE id IN (108, 208, 134, 142) ORDER BY FIELD(id, 108, 208, 134, 142);

You have to change your query to this.

SELECT * FROM `challenges` 
WHERE id IN (108, 208, 134, 142)
ORDER BY ID

If you want them ordered in the same way as in the in statement, regardless of numerical ordering, you can use the following.

SELECT * FROM `challenges` 
WHERE id IN (108, 208, 134, 142)
ORDER BY FIELD(id,108,208,134,142)

There is no guarantee that the result come out in order, you should explicitly specify an ORDER BY clause. If you want to sort by id value:

SELECT * FROM `challenges` WHERE id IN (108, 208, 134, 142) ORDER BY id

for an arbitrary order:

SELECT * FROM `challenges` WHERE id IN (108, 208, 134, 142)
  ORDER BY FIELD( id, 108, 208, 134, 142 )