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

Previous Page Print Page