MySQL - ORDER BY values within IN()

mysql sql-order-by where-clause
Matt picture Matt · Jun 6, 2009 · Viewed 82.1k times · Source

I'm hoping to sort the items returned in the following query by the order they're entered into the IN() function.

INPUT:

SELECT id, name FROM mytable WHERE name IN ('B', 'A', 'D', 'E', 'C');

OUTPUT:

|   id   |   name  |
^--------^---------^
|   5    |   B     |
|   6    |   B     |
|   1    |   D     |
|   15   |   E     |
|   17   |   E     |
|   9    |   C     |
|   18   |   C     |

Any ideas?

Answer

Ayman Hourieh picture Ayman Hourieh · Jun 6, 2009 
SELECT id, name
FROM mytable
WHERE name IN ('B', 'A', 'D', 'E', 'C')
ORDER BY FIELD(name, 'B', 'A', 'D', 'E', 'C')

The FIELD function returns the position of the first string in the remaining list of strings.

However, it is much better performance-wise to have an indexed column that represents your sort order, and then sort by this column.

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