MySQL: How to Order a SQL query’s results by a given ID Sequence CodeUnit 01 AUG 2011

I have a specific sequence of ID numbers and I want the result set retrieved when looking up more information on those records in the exact same sequence that I have it. This sequence is not your usual ascending or descending order, but rather in a very specific order. So how does one do this?

Well the answer lies with the useful FIELD SQL function, normally a string function but one that proves to be quite handy when it comes to numbers to. So let’s lay out an example shall we:

Given the ID sequence of 3, 1, 5, 2, 7 the normal way to retrieve say the name field would be:

SELECT `name` FROM `table` WHERE `id` IN (3,1,5,2,7);

Of course, this would return a result set in no particular order, simply in the order in which the database was able to retrieve the results.

Now for our example, we want to retrieve the list of names in the exact order of that given ID sequence, changing our SQL statement to this:

SELECT `name` FROM `table` WHERE `id` IN (3,1,5,2,7) ORDER BY FIELD(`id`,3,1,5,2,7);

The resulting resultset will have the names ordered in the exact sequence you wanted them – which is obviously very nifty!

Related Posts:

About Craig Lotter

South African software architect and developer at Touchwork. Husband to a cupcake baker and father to two little girls. I don't have time for myself any more.