Get the returned record set order in MySQL IN clause?

MySQLMySQLi Database

For returned record set order, you need to use FIND_IN_SET() function.

 For an example, let us create a table.

mysql> create table returnRecordSetOrderDemo
   -> (
   -> Id int,
   -> Name varchar(20)
   -> );
Query OK, 0 rows affected (1.01 sec)

Insert some records in the table using insert command.

The query is as follows.

mysql> insert into returnRecordSetOrderDemo values(100,'John');
Query OK, 1 row affected (0.13 sec)
mysql> insert into returnRecordSetOrderDemo values(130,'Carol');
Query OK, 1 row affected (0.13 sec)
mysql> insert into returnRecordSetOrderDemo values(103,'Bob');
Query OK, 1 row affected (0.17 sec)
mysql> insert into returnRecordSetOrderDemo values(134,'Sam');
Query OK, 1 row affected (0.27 sec)
mysql> insert into returnRecordSetOrderDemo values(102,'Larry');
Query OK, 1 row affected (0.15 sec)
mysql> insert into returnRecordSetOrderDemo values(145,'David');
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement.

The query is as follows.

mysql> select *from returnRecordSetOrderDemo;

The following is the output.

+------+-------+
| Id   | Name  |
+------+-------+
| 100  | John  |
| 130  | Carol |
| 103  | Bob   |
| 134  | Sam   |
| 102  | Larry |
| 145  | David |
+------+-------+
6 rows in set (0.00 sec)

Here is the query for MySQL 'IN' clause and the returned record set order.

mysql> select *from returnRecordSetOrderDemo
-> where Id in(100,145,103,130)
-> order by FIND_IN_SET(Id,'100,145,103,130');

The following is the output.

+------+-------
| Id   | Name |
+------+-------+
| 100  | John  |
| 145  | David |
| 103  | Bob   |
| 130  | Carol |
+------+-------+
4 rows in set (0.00 sec)
raja
Published on 29-Mar-2019 11:13:42
Advertisements