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.