Using FIELD() in ORDER BY clause - MySQL

Question

This is with reference to the answer given by Ayman Hourieh

to the question: MySQL - ORDER BY values within IN()

SELECT id, name
FROM mytable
WHERE name IN ('B', 'A', 'D', 'E', 'C')
ORDER BY FIELD(name, 'B', 'A', 'D', 'E', 'C')

The FIELD function returns the position of the first string in the remaining list of strings.

It will thus be something like .. ORDER BY 4 or ORDER BY 1 as the FIELD function returns the position. Please explain how does ORDER BY work in this situation.

Answer 1

For each row, FIELD() returns an integer value. The rows are then ordered by this value.

Adding the FIELD() clause to the select part should help you understand how it works:

SELECT id, name, FIELD(name, 'B', 'A', 'D', 'E', 'C')
FROM mytable
WHERE name IN ('B', 'A', 'D', 'E', 'C')
ORDER BY FIELD(name, 'B', 'A', 'D', 'E', 'C')

Sample result set:

 4 | Z | 0
12 | B | 1
 1 | B | 1
 6 | A | 2
 3 | E | 4