1. Problem.

I want to query some specific records in database and sort it by the specify order (not asc or desc).
For example, I have a users table with attributes id, user_name.

I want to query records that have id = 1, 2, 5 and returned records will be sorted by user_name = B then A then E.

2. Solution.

Solution 1: Use CASE ... WHEN to modify user_name before sort

SELECT * FROM users 
  WHERE id IN (1, 2, 5)
  ORDER BY 
     CASE 
     WHEN user_name = 'B' THEN 1
     WHEN user_name = 'A' THEN 2
     WHEN user_name = 'E' THEN 3
     ELSE 4 
  END

Solution 2: Use mysql's ORDER BY FIELD

SELECT * FROM users
  WHERE id IN (1, 2, 5)
  ORDER BY FIELD (user_name, 'B', 'A', 'E')

The solution 2 is shorter and simpler, it guarantee the record order in the list is correct and the other records will keep the same order and lies above of order record.

3. How ORDER BY FIELD work?

The way ORDER BY FIELD work is exact the same as CASE ... WHEN
The FIELD function will return index with each value in its list. For example B, A, E will have index 1, 2, 3 respectively. And the records with value not in the list will be receive d value 0.

4. Some function that can work with ORDER BY FIELD.

IF() function: As describe above the records with value not in the FIELD list always lie on top of the result. If we want the record in the FIELD list on top of the result instead we can use IF() function, for example:

SELECT * FROM users
ORDER BY IF(FIELD(id, 1, 3, 4)=0, 1, 0), FIELD(id, 1, 3, 4);

Of those in:

IF(FIELD(id, 1, 3, 4)=0, 1, 0) is shorten for

if FIELD(id, 1, 3, 4) == 0
  return 1
else 
  return 0
end

so we will have sort_index like this:

because IF clause is the first clause so the record with sort_index = 0 will be bubble up.

*Note: Both ORDER BY FIELD and ORDER CASE ... WHEN recalculate the value before sort, so the query can not use indexes.