Ordering by specific field

Today I had a developer ask me how they could get their values to return in a specific order with a LIMIT clause. Developer had a query like:

SELCT name, type
FROM foo
WHERE type_id IN (10,2,5,4)
LIMIT 5

I told him you needed to add an order by clause before the LIMIT to get it ordered the way you want it. Then developer said he wanted to give certain type_id's priority and to be at the top of the list. I hadn't had a need to do before and so after some research discovered the "FIELD" option. When added to an ORDER BY into the query like this you can accomplish what this:


SELCT name, type
FROM foo
WHERE type_id IN (10,2,5,4)
ORDER BY FIELD(10,2,5,4, type_id) ASC
LIMIT 5

Developer is happy he can do what he wanted in MySQL and not have to script it out.

Here are some good examples:

http://stackoverflow.com/questions/958627/mysql-order-by-values-within-in


http://www.electrictoolbox.com/mysql-order-specific-field-values/