Select query using MySQL IN() and avoid sorting in it

MySQLMySQLi Database

Using IN() sorts the result for the specific field. To avoid this, use ORDER BY and FIND_IN_SET() for the field.

To understand the find_in_set(), let us create a table. The query to create a table is as follows −

mysql> create table ProductStock
   -> (
   -> ProductId int,
   -> ProductName varchar(20),
   -> ProductQuantity int,
   -> ProductPrice float
   -> );
Query OK, 0 rows affected (0.79 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into ProductStock values(1,'Product-101',10,500.56);
Query OK, 1 row affected (0.20 sec)
mysql> insert into ProductStock values(25,'Product-111',5,150.00);
Query OK, 1 row affected (0.21 sec)
mysql> insert into ProductStock values(67,'Product-311',7,1000.50);
Query OK, 1 row affected (0.60 sec)
mysql> insert into ProductStock values(55,'Product-561',8,900.00);
Query OK, 1 row affected (0.24 sec)
mysql> insert into ProductStock values(75,'Product-221',15,670.56);
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from ProductStock;

Output

+-----------+-------------+-----------------+--------------+
| ProductId | ProductName | ProductQuantity | ProductPrice |
+-----------+-------------+-----------------+--------------+
|         1 | Product-101 |              10 |       500.56 |
|        25 | Product-111 |               5 |          150 |
|        67 | Product-311 |               7 |       1000.5 |
|        55 | Product-561 |               8 |          900 |
|        75 | Product-221 |              15 |       670.56 |
+-----------+-------------+-----------------+--------------+
5 rows in set (0.00 sec)

Here is the query to use find_in_set() for your query −

mysql> select *from ProductStock
   -> where ProductId IN(25,55,67,75,1)
   -> order by find_in_set(ProductId,'25,55,67,75,1');

Output

+-----------+-------------+-----------------+--------------+
| ProductId | ProductName | ProductQuantity | ProductPrice |
+-----------+-------------+-----------------+--------------+
|        25 | Product-111 |               5 |          150 |
|        55 | Product-561 |               8 |          900 |
|        67 | Product-311 |               7 |       1000.5 |
|        75 | Product-221 |              15 |       670.56 |
|         1 | Product-101 |              10 |       500.56 |
+-----------+-------------+-----------------+--------------+
5 rows in set (0.31 sec)
raja
Published on 19-Mar-2019 06:49:12
Advertisements