How to Retrieve SQL Query Results in a Predefined Order Without Using ORDER BY?

Returning Query Results in Predefined Order

In SQL, it is typically challenging to retrieve query results in a particular order unless specified using the ORDER BY clause. However, in some scenarios, the ORDER BY clause may not be applicable, such as when attempting to retrieve data in a predetermined order based solely on a specific field's values.

A recent discussion brought up a scenario where it was desired to select IDs (7, 2, 5, 9, and 8) and retrieve them in that exact order without relying on any additional criteria. The following queries both return the results in an unpredictable order:

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

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

A novel solution was discovered through a blog entry that elegantly addresses this issue:

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

The FIND_IN_SET function is utilized to return the position of a specified value within a given set. For instance, id 7 has a position of 1, id 2 has a position of 2, and so on.

When applying the ORDER BY clause with FIND_IN_SET, the ORDER BY statement understands the positions and effectively orders the results based on the specified values. This solution leverages an internal mechanism in MySQL to achieve the desired predetermined order.

It's worth noting that this technique can be a valuable alternative when the ORDER BY clause cannot be employed to specify the desired order.

The above is the detailed content of How to Retrieve SQL Query Results in a Predefined Order Without Using ORDER BY?. For more information, please follow other related articles on the PHP Chinese website!