How to Maintain Row Order in SQL Queries with WHERE IN Clause?

Controlling Row Order with WHERE IN Clause

In a Structured Query Language (SQL) statement that utilizes the WHERE IN clause, the rows returned may not always adhere to the order specified within the clause.

Preserving Row Order with FIELD() Function

To rectify this, one can employ the FIELD() function in conjunction with the ORDER BY clause. The FIELD() function assigns a position to each row based on the specified order of values within the IN clause. By ordering the rows according to this assigned position, one can preserve the original order from the WHERE IN clause.

For instance, consider the following SQL query:

SELECT *
FROM table
WHERE id IN (118, 17, 113, 23, 72);

This query returns the rows in an ascending order based on the ID column, ignoring the order specified in the WHERE IN clause. To obtain the rows in the order specified by the IN clause, execute the following query:

SELECT *
FROM table
WHERE id IN (118, 17, 113, 23, 72)
ORDER BY FIELD(id, 118, 17, 113, 23, 72);

In this query, the ORDER BY clause arranges the rows according to the order of the values (118, 17, 113, 23, 72) specified in the FIELD() function. Therefore, the returned rows will adhere to the sequence defined in the WHERE IN clause.

The above is the detailed content of How to Maintain Row Order in SQL Queries with WHERE IN Clause?. For more information, please follow other related articles on the PHP Chinese website!