Syntax error Get the returned record set order in MySQL IN clause?

Get the returned record set order in MySQL IN clause?

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)
Updated on: 2019-07-30T22:30:25+05:30

154 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements