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 as below. This is a somewhat simple table but it can be used to illustrate the point in this post quite well.
CREATE TABLE fruits (`id` int, `name` varchar(50));
INSERT INTO fruits (`id`, `name`)
VALUES (1, 'Apple'),
(2, 'Durian'),
(3, 'Banana'),
(4, 'Lemon'),
(5, 'Pear'),
(6, 'Star fruit'),
(7, 'Strawberry'),
(8, 'Orange');
Ordering by specific field values
The fruit table has a “name” field with the following unique id: Apple, Durian, Banana and etc. Let’s say, for argument’s sake, that we want to order the data in a specific id order by Banana, Apple, Orange and then Lemon. 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 fruits WHERE id IN (1, 3, 8, 4) ORDER BY FIELD(name, 'Banana', 'Apple', 'Orange', 'Lemon')
The results from the example table will look like this:
+----+--------+ | id | name | +----+--------+ | 3 | Banana | | 1 | Apple | | 8 | Orange | | 4 | Lemon | +----+--------+
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.