How to Order Query Results Based on a Specific List of IDs in MySQL?

Customizing Query Results Order with FIND_IN_SET

While it might seem intuitive to expect that a SELECT statement would preserve the order of specified IDs, by default, MySQL will return results in an arbitrary sequence. However, with a clever technique using the FIND_IN_SET function, you can override this behavior and establish a predetermined order for your results.

Consider the following query, which aims to retrieve IDs 7, 2, 5, 9, and 8:

SELECT id FROM table WHERE id in (7,2,5,9,8);

Regardless of the order of the IDs in the WHERE clause, the results will always be returned in the table's internal order. To customize the order, you can leverage the FIND_IN_SET function:

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

By providing a comma-separated list of the desired order in the second argument of FIND_IN_SET, you essentially create a ranking. Each ID's position in the list determines its order in the result set.

For example, the query below will order the results in the order specified by the second argument:

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

Using this technique, you have precise control over the order of your query results, giving you the flexibility to arrange data based on a predefined sequence.

The above is the detailed content of How to Order Query Results Based on a Specific List of IDs in MySQL?. For more information, please follow other related articles on the PHP Chinese website!