Ben Reichelt’s Weblog

I recently needed to do a sql query in MySql that selected a bunch of items by their ID, the query was something like this:

SELECT * FROM Customer
WHERE CustomerID IN (502, 506, 398)

I just wanted some customers (more like 50, not just 3), but I also wanted them sorted in the exact order that I specified their ID’s by, so I wanted customer 502, then 506, and then 398, in that order.

It turns out theres a feature in MySql that allows you to specify an arbitrary sort order in a query, it looks like this:

SELECT * FROM Customer
WHERE CustomerID IN (502, 506, 398)
ORDER BY FIELD(CustomerID, 502, 506, 398)

You simply specify the field that you want to sort on, and then give the sort order of the items you want to use. You don’t even have to be selecting on the field that you order by, so this would also work:

SELECT FirstName, LastName FROM Customer
WHERE CustomerID IN (502, 506, 398)
ORDER BY FIELD(CustomerID, 502, 506, 398)

Here, I’m not including CustomerID in the results, but I can still use it in my custom sort.