How to Sort MySQL Results by the Order Specified in the IN() Clause?

MySQL: Sorting Results by Order of IN() Specification

When retrieving data with an IN() clause, maintaining the order in which values were specified can enhance the output's readability and usability. This article explores a solution to sort the results based on the sequence of values within the IN() function.

Consider the following query:

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

This query returns the following output, where the ordering of the rows is not consistent with the order in the IN() clause:

| id | name |
^--------^---------^
| 5 | B |
| 6 | B |
| 1 | D |
| 15 | E |
| 17 | E |
| 9 | C |
| 18 | C |

To sort the results in the order specified in the IN() clause, we can utilize the FIELD function. The FIELD function takes a value as its first argument and returns the position of that value in the remaining list of arguments.

The following query uses the FIELD function to sort the results based on the position of the 'name' column values in the list ('B', 'A', 'D', 'E', 'C'):

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

This query returns the following output:

| id | name |
^--------^---------^
| 5 | B |
| 6 | B |
| 1 | D |
| 15 | E |
| 17 | E |
| 9 | C |
| 18 | C |

As you can see, the rows are now ordered in the sequence that was originally specified in the IN() clause.

The above is the detailed content of How to Sort MySQL Results by the Order Specified in the IN() Clause?. For more information, please follow other related articles on the PHP Chinese website!