Sorting in SQL select statement

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:

Id Animal
1 Zebra
2 Giraffe
3 Lion
4 Tiger
5 Sparrow
6 Horse
7 Chicken
8 Caw
9 Duck
10 Dog

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.

Comments

Hello! cialis vs viagra , buy

Hello! cialis ,

Hello!
cialis ,

Hello! tramadol ,

Hello!
tramadol ,

Hello! tramadol ,

Hello!
tramadol ,

Hello! viagra ,

Hello!
viagra ,

Hello! phentermine ,

Hello!
phentermine ,

Hello! cialis online ,

Hello!
cialis online ,

Hello! cialis in the uk ,

Hello!
cialis in the uk ,

Hello! buy cialis ,

Hello!
buy cialis ,

Hello! cialis online discount

Hello! viagra cialis levitra

Hello! tramadol , phentermine

Hello! xanax , viagra ,

Hello!
xanax , viagra , phentermine , viagra , xanax ,

Hello! phentermine , tramadol

Hello! tramadol ,

Hello!
tramadol ,

Hello! xanax ,

Hello!
xanax ,

Hello! viagra ,

Hello!
viagra ,

Hello! cialis ,

Hello!
cialis ,

Hello! tramadol ,

Hello!
tramadol ,

Hello! cialis reviews ,

Hello!
cialis reviews ,

Hello! viagra sale ,

Hello!
viagra sale ,

Hello! cialis soft tabs ,

Hello!
cialis soft tabs ,

Hello! cialis impotence drug

Hello! free viagra ,

Hello!
free viagra ,

Hello! cialis soft tabs ,

Hello! xanax , viagra ,

Hello! phentermine , viagra ,

Hello! viagra , xanax ,

Hello! viagra , phentermine ,

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.