Artur Hefczyc

It might be nothing new for SQL experts but I am mainly Java developer and I have just discovered a way to sort SQL results according to the specified order rather than natural order. Whether it does make sense or not depends on a particular case, usually it doesn't but in very special use cases it might be very helpful.

Problem description

The question is how to sort select results according to specified order rather than natural order. Let's say we have a table animals with thousands of entries which looks like this:

We want now to select a few records from the table and display them in different from the natural order. Let's say in following order: 3, 4, 10, 7, 9, 5, 6, 1, 2, 8

Problem solution

We can do this using following query:

select  Animal from animals order by field(id, 3, 4, 10, 7, 9, 5, 6, 1, 2, 8);

If there are indeed thousands of entries in the table all of them will be displayed and only records with specified IDs will be listed in specified order. To select only those 10 above entries we need to add where clause to the query:

select  Animal from animals where id in (3, 4, 10, 7, 9, 5, 6, 1, 2, 8) order by field(id, 3, 4, 10, 7, 9, 5, 6, 1, 2, 8);

I hope this is useful to somebody. I confirmed it working with MySQL database and I am not sure whether it works with any other SQL database.