MySQL Order the results by a predefined sequence

Sometimes you wanna that the results come following a predefined sequence, for example following one sequence of ID’s (10, 20, 4, 123, 3)…

So how order this? You can not use order as DESC, or ASC… perhaps you can create some procedure or function to do it for you but there is a simple way to do this without create a function or procedure, using the native function FIELD().

So for our example the query will looks like:

1

SELECT * FROM our_table WHERE id IN (10, 20, 4, 123, 3) ORDER BY FIELD(id, 10, 20, 4, 123, 3)

It’s also possible using with strings for example:

1

SELECT * FROM our_table ORDER BY FIELD(type, 'car', 'bus', 'motorcycle')

That’s it, the function FIELD can be useful in otherways as well, you can check it in the documentation.