MySQL ordering results by specific field values

In MySQL we can sort the results in ascending or descending order very easily by using the ORDER BY clause. However, there are times when you want to sort the results in a specific order which cannot be done using the ASC or DSC. FIELD() of MySQL ORDER BY clause can be used to sort the results in a specific order.
For this post I will use my example MySQL table and data. This is just a sample table with sample data, used to represent what I need. So, in this table I have different species of pets and I need to get a list of all pets in the order dogs, cat, snake and bird. I cannot use the MySQL ORDER BY clause to fetch the results in this order. By using the FIELD() function of the MySQL ORDER BY clause I can get the results in the order I need. Here is the query I can use.
SELECT name, species FROM `pet`
ORDER BY FIELD(species, 'dog','cat','snake','bird'), name ASC


This query would return me the results ordered by the species in the order I need and then sort it by name. Here is the result:

name species
Bowser dog
Buffy dog
Fang dog
Claws cat
Fluffy cat
Slim snake
Chirpy bird
Whistler bird
The above solution works fine as along as there are no other values in the table for species apart from what specified in the FIELD function. In case there are other values and you need them to appear last in the list then we will need to change the query. Using the same table, consider that we want to sort the results by dog and cat first and anything else after then we can use the following query:
SELECT name, species FROM `pet` 
ORDER BY FIELD(species, 'cat','dog') DESC , name ASC
This will give us the following results:

name species
Bowser dog
Buffy dog
Fang dog
Claws cat
Fluffy cat
Chirpy bird
Slim snake
Whistler bird
Notice that the results are sorted by dog and cat, but not by any other species. If we need to have the other results also in group we can use the following query:
SELECT name, species FROM `pet` 
ORDER BY FIELD(species, 'cat','dog') DESC, species ASC, name ASC
Now this query returns us the following results:

name species
Bowser dog
Buffy dog
Fang dog
Claws cat
Fluffy cat
Chirpy bird
Whistler bird
Slim snake
Note: I searched the MySQL documentation but I am unable to find the proper documentation for this function. However, I have tried this on MySQL 5.0, 5.1, 5.5 and it works in all of them.
  1. You are an angel for me. I have wasted my whole night in this problem. m newbie :(… thx for the help…Bookmarking it.

  2. OMg thank a lot for this i searched a long time for this.

  3. Documentation for the FIELD() function is here:

    http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field

    Thanks for the post.

Leave a Reply

Trackbacks and Pingbacks: