How can I return MySQL query results in a predefined order using FIND_IN_SET()?

Returning Query Results in Predefined Order

When performing a SELECT statement, it is often desirable to retrieve records in a specific order. However, by default, MySQL returns results in an arbitrary order based on the table's internal organization.

To override this default behavior and return results in a predefined order, MySQL provides a method using the FIND_IN_SET() function. This function takes two arguments: a search string and a set of values. It returns the position of the search string within the set, or 0 if the string is not found.

By leveraging FIND_IN_SET(), it is possible to execute a SELECT statement and specify the desired order of the results. For instance, to retrieve records with IDs 7, 2, 5, 9, and 8 in that specific order:

SELECT id
FROM table
WHERE id IN (7, 2, 5, 9, 8)
ORDER BY FIND_IN_SET(id, "7,2,5,9,8");

This query returns the following result:

id
---
7
2
5
9
8

Note that the ID values are returned in the order specified in the second argument of FIND_IN_SET(), regardless of the order in which they appear in the WHERE clause.

This technique provides a powerful way to control the order of query results, even when the underlying table does not have an explicitly defined sort order.

The above is the detailed content of How can I return MySQL query results in a predefined order using FIND_IN_SET()?. For more information, please follow other related articles on the PHP Chinese website!