MySQL sort after argument in IN()

Question

I have string containing a lot of IDs corresponding to my database. Like:

1,2,3,4,5

I then do a mySQL query to select all those rows:

SELECT * FROM `table` WHERE `id` IN (".$myIDs.")

I want mySQL to return the rows in the order they are in my IN() select.

So if I instead had

2,1,3,4,5

I would have #2 as first row, when fetching in PHP. Without any ORDER BY it looks like it return the lowest ID first.

Let me know if you need further exlpanation.

Answer 1

You should be able to do this via the FIELD() function like so:

SELECT * FROM `table` WHERE `id` IN (2,1,3,4,5) ORDER BY FIELD(`id`, 2,1,3,4,5) DESC

That is:

SELECT
  *
FROM
  `table`
WHERE
  `id` IN (".$myIDs.")
ORDER BY
  FIELD(`id`, ".$myIDs.") DESC

More in this blog post: Sorting MySQL rows using column values.