MySQL specifying exact order with WHERE `id` IN (...)

mysql sql-order-by in-clause
Gray Fox picture Gray Fox · Jun 2, 2010 · Viewed 11.3k times · Source

Is there an easy way to order MySQL results respectively by WHERE id IN (...) clause? Example:

SELECT * FROM articles WHERE articles.id IN (4, 2, 5, 9, 3)

to return

Article with id = 4
Article with id = 2
Article with id = 5
Article with id = 9
Article with id = 3

and also

SELECT * FROM articles WHERE articles.id IN (4, 2, 5, 9, 3) LIMIT 2,2

to return

Article with id = 5
Article with id = 9

Update: to be more specific, I want to avoid tampering with the data in parentheses in WHERE articles.id IN (4, 2, 5, 9, 3), since those IDs are dynamic and automatically ordered.

Answer

bobince picture bobince · Jun 2, 2010

Yeah, kind of:

SELECT * FROM articles
WHERE articles.id IN (4, 2, 5, 9, 3)
ORDER BY FIND_IN_SET(articles.id, '4,2,5,9,3')

but this is non-standard SQL and smells a bit.

Related questions

How does MySQL process ORDER BY and LIMIT in a query?

I have a query that looks like this: SELECT article FROM table1 ORDER BY publish_date LIMIT 20 How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by …

Read More
MySQL order by before group by

There are plenty of similar questions to be found on here but I don't think that any answer the question adequately. I'll continue from the current most popular question and use their example if that's alright. The task in this …

Read More
Order a MySQL table by two columns

How do I sort a MySQL table by two columns? What I want are articles sorted by highest ratings first, then most recent date. As an example, this would be a sample output (left # is the rating, then the article …

Read More