Ordering by specific field values with MySQL

There may be times when a specific order is required in a SQL query which cannot be done using either ASC or DESC or using a special sort field. MySQL has a ORDER BY FIELD function which can be used to do this.

Example Data

The example data in this post uses my example fruit table. This is a somewhat simple table but it can be used to illustrate the point in this post quite well.

Ordering by specific field values

The fruit table has a "name" field with the following unique values: Apple, Banana, Orange, Pear. Each of these unique values has a set of varieties.

Let's say, for argument's sake, that we want to order the data in a specific order by Banana, Apple, Pear, Orange and then by their varieties. It's not possible to do this using a regular ORDER BY clause because an ascending or descending sort on this field will not work. We would either need some form of sort column or another alternative.

Using the FIELD( ) function in the ORDER BY clause we can achieve this. It works by specifying the column to sort by and then the values to sort in order. For example:

SELECT * FROM fruit 
ORDER BY FIELD(name, 'Banana', 'Apple', 'Pear', 'Orange'), variety;

The resulting data from the example table looks like this:

+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
|       10 | Banana | Plantain            |
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        1 | Apple  | Red Delicious       |
|        8 | Pear   | Anjou               |
|        4 | Pear   | Bartlett            |
|        2 | Pear   | Comice              |
|        5 | Orange | Blood               |
|        3 | Orange | Navel               |
|        9 | Orange | Valencia            |
+----------+--------+---------------------+

The gotcha

There is a slight "gotcha" when using this function. Any values that are in the column that are not in the FIELD() function will appear in a more or less random order before the specified values. For example, only specifying Apple and Banana:

SELECT * FROM fruit  
ORDER BY FIELD(name, 'Banana', 'Apple') DESC, variety;

This results in:

+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        1 | Apple  | Red Delicious       |
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
|       10 | Banana | Plantain            |
|        8 | Pear   | Anjou               |
|        4 | Pear   | Bartlett            |
|        5 | Orange | Blood               |
|        2 | Pear   | Comice              |
|        3 | Orange | Navel               |
|        9 | Orange | Valencia            |
+----------+--------+---------------------+

A solution to the gotcha

Although you would normally only use this function when the exact columns are known, a solution is to reverse the order of the specified fields and order them in a descending order, and then do a second sort on the same field.

The following example, despite how it looks, actually sorts in order of Banana, then Apple, and then the other names in ascending order:

SELECT * FROM fruit  
ORDER BY FIELD(name, 'Apple', 'Banana') DESC, name, variety;

This results in:

+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
|       10 | Banana | Plantain            |
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        1 | Apple  | Red Delicious       |
|        5 | Orange | Blood               |
|        3 | Orange | Navel               |
|        9 | Orange | Valencia            |
|        8 | Pear   | Anjou               |
|        4 | Pear   | Bartlett            |
|        2 | Pear   | Comice              |
+----------+--------+---------------------+

This can be a useful solution if a specific set of rows needs to appear before the others in the resultset, but it would not normally appear first when using an ASC or DESC sort order.

Related posts:

• Randomly ordering data with MySQL with a random value column (Wednesday, March 3rd 2010)
• MySQL's LIMIT syntax can be slow for large offsets (Wednesday, February 17th 2010)
• An alternative to ORDER BY RAND() for MySQL (Wednesday, February 3rd 2010)