" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
 - Mahatma Gandhi

  1. Home
  2. Database Tips and Tricks
  3. Using FIELD function in ORDER BY clause in MySQL

Using FIELD function in ORDER BY clause in MySQL

Introduction to FIELD function:

FIELD(str, str1, str2, str3, …) function search for ‘str’ in the list ‘str1,str2,str3…’ and returns the index (position) of str in the list.
If ‘str’ is not present in the list 0 is returned.
If ‘str’ is NULL, it returns 0.

Example:
SELECT FIELD(‘ab’, ‘abc’, ‘ab’, ‘rabdf’, ‘fab’, ‘rtg’);
output: 2
SELECT FIELD(‘ab’, ‘rf’, ‘hfy’, ‘rabr’, ‘abf’, ‘gdabf’);
output: 0

Use of FIELD function in ORDER BY clause:
Sometimes you might have felt the need of sorting the sql query result in custom order instead of sorting in ASC/DESC order of some field. This is where FIELD function can be used effectively. Lets look at some examples.
Simple order by example:
SELECT id, name FROM color ORDER BY id;
Output:

+——+——–+
|   id | name   |
+——+——–+
|    1 | White  |
|    2 | Black  |
|    3 | Yellow |
|    4 | Green  |
|    5 | Red    |
|    6 | Orange |
+——+——–+

 

Same query with use of FIELD function
SELECT id, name FROM color ORDER BY FIELD(name, ‘Red’, ‘Green’, ‘Orange’, ‘White’, ‘Yellow’, ‘Black’);
Output:

+——+——–+
|   id | name   |
+——+——–+
|    1 | White  |
|    2 | Black  |
|    3 | Yellow |
|    4 | Green  |
|    5 | Red    |
|    6 | Orange |
+——+——–+

NOTE: If there are values which is present in the table but not passed to the FIELD function, then they will appear towards the end of the output in random order.
SELECT id, name FROM color ORDER BY FIELD(name, ‘Red’, ‘Green’);
Output:
+——+——–+
|   id | name   |
+——+——–+
|    5 | Red    |
|    4 | Green  |
|    1 | White  |
|    6 | Orange |
|    3 | Yellow |
|    2 | Black  |
+——+——–+

Solution to this problem:
Wecan add one more field name in order clause. For present example we can add either id or name according to the requirement.
SELECT id, name FROM color ORDER BY FIELD(name, ‘Red’, ‘Green’), name;
Output:
+——+——–+
|   id | name   |
+——+——–+
|    5 | Red    |
|    4 | Green  |
|    2 | Black  |
|    6 | Orange |
|    1 | White  |
|    3 | Yellow |
+——+——–+
Runish Kumar, Sr Developer, Mindfire Solutions.
Related Tags: CUSTOM FIELD VALUE, MySQL, Order By, QUERY
Author : Runish Kumar

Leave a Reply