How to select MySQL rows in the order of IN clause?

You need to use FIND_IN_SET() function to select MySQL rows in the order of IN clause. The syntax is as follows −

SELECT yourVariableName.*
FROM yourTableName yourVariableName
WHERE yourVariableName.yourColumnName IN(value1,value2,...N)
ORDER BY FIND_IN_SET( yourVariableName.yourColumnName,'value1,value2,...N');

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table InDemo
   -> (
   -> CodeId int,
   -> Name varchar(20)
   -> );
Query OK, 0 rows affected (0.95 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into InDemo values(1,'John');
Query OK, 1 row affected (0.24 sec)
mysql> insert into InDemo values(2,'Carol');
Query OK, 1 row affected (0.19 sec)
mysql> insert into InDemo values(3,'Sam');
Query OK, 1 row affected (0.18 sec)
mysql> insert into InDemo values(4,'Bob');
Query OK, 1 row affected (0.17 sec)

Now you can display all records from the table using select statement. The query is as follows −

mysql> select *from InDemo;

The following is the output −

+--------+-------+
| CodeId | Name  |
+--------+-------+
|      1 | John  |
|      2 | Carol |
|      3 | Sam   |
|      4 | Bob   |
+--------+-------+
4 rows in set (0.00 sec)

Here is the query to select MySQL rows in the order of IN clause −

mysql> select tbl.*
   -> from InDemo tbl
   -> where tbl.CodeId in(1,3,2,4)
   -> ORDER BY FIND_IN_SET( tbl.CodeId,'1,3,2,4');

The following is the output −

+--------+-------+
| CodeId | Name  |
+--------+-------+
|      1 | John  |
|      3 | Sam   |
|      2 | Carol |
|      4 | Bob   |
+--------+-------+
4 rows in set (0.00 sec)

Samual Sam

Published on 27-Feb-2019 08:35:56

• Related Questions & Answers
• How to order or choose rows in MySQL GROUP BY clause?
• Sort by order of values in a MySQL select statement IN clause?
• How to select the last three rows of a table in ascending order with MySQL?
• How to order results of a query randomly & select random rows in MySQL?
• MySQL query to select rows except first row in descending order?
• How can I use RAND() function in an ORDER BY clause to shuffle MySQL set of rows?
• What is the use of ORDER BY clause in MySQL?
• How select specific rows in MySQL?
• How to use union and order by clause in MySQL?
• How to select last two rows in MySQL?
• Get the returned record set order in MySQL IN clause?
• How to update multiple rows using single WHERE clause in MySQL?
• How do I SELECT none of the rows and columns in MySQL?
• How Groups function can be used in MySQL SELECT clause?
• How to order MySQL rows by multiple columns?