Sorting in SQL select statement
- Language:
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.
- artur's blog
- Add new comment
- 16227 reads
Comments
Hi, Thank you very much for
Hi,
Thank you very much for your solution...i used old method now i correct my self..its very useful for me i searched many times but i can't get the solution for the custom search...
my old query is: