Tag Archives: Order By Field

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

Read more »