Implementation of MySQL Custom List Sorting by Specified Fields

Problem description

As we all know, SQL arranged in ascending order of a certain field in MySQL is (take id as an example, the same below):

SELECT * FROM `MyTable`
WHERE `id` IN (1, 7, 3, 5)
ORDER BY `id` ASC

The SQL in descending order is:

SELECT * FROM `MyTable`
WHERE `id` IN (1, 7, 3, 5)
ORDER BY `id` DESC

Sometimes the above ranking does not meet our needs. For example, we want to arrange id in the order of 5, 3, 7 and 1. This is also one of the problems that many domestic and foreign counterparts often encounter.

Below we give a solution to sort by a field in the table in the way we want the list.

Solutions

Sort by Field (ORDER BY FIELD).

Grammar

ORDER BY FIELD(`id`, 5, 3, 7, 1)

Note that there is no space after FIELD.

Therefore, the complete SQL is:

SELECT * FROM `MyTable`
WHERE `id` IN (1, 7, 3, 5)
ORDER BY FIELD(`id`, 5, 3, 7, 1)

Common Applications

SELECT * FROM `MyTable`
WHERE ` name ` IN ('Zhang 3', 'Li 4', 'Wang 5', 'Sun 6')
ORDER BY FIELD (` name ',' Li 4 ',' Sun 6 ',' Zhang 3 ',' Wang 5 ')