Maintaining order in MySQL “IN” query?

You can maintain the order in MySQL IN query with the help of field command. The syntax is as follows −

select *from yourTableName anyVariableName where anyVariableName.yourColumnName in(value1,value2,......N)
order by field(anyVariableName.yourColumnName,value1,value2,......N);

To implement the above syntax let us create a table −

mysql> create table OrderInDemo
   −> (
      −> Id int,
      −> Name varchar(100),
      −> Age int
   −> );
Query OK, 0 rows affected (1.24 sec)

Now let us insert some records in the table. The query to insert records are as follows −

mysql> insert into OrderInDemo values(90,'David',23);
Query OK, 1 row affected (0.10 sec)

mysql> insert into OrderInDemo values(9,'Sam',24);
Query OK, 1 row affected (0.59 sec)

mysql> insert into OrderInDemo values(10,'Carol',19);
Query OK, 1 row affected (0.25 sec)

mysql> insert into OrderInDemo values(1,'John',26);
Query OK, 1 row affected (0.42 sec)

mysql> insert into OrderInDemo values(3,'Johnson',25);
Query OK, 1 row affected (0.18 sec)

mysql> insert into OrderInDemo values(2,'Ramit',20);
Query OK, 1 row affected (0.18 sec)

Display all records with the help of select statement. The query is as follows −

mysql> select *from OrderInDemo;

The following is the output −

+------+---------+------+
| Id   | Name    | Age  |
+------+---------+------+
|   90 | David   |   23 |
|    9 | Sam     |   24 |
|   10 | Carol   |   19 |
|    1 | John    |   26 |
|    3 | Johnson |   25 |
|    2 | Ramit   |   20 |
+------+---------+------+
6 rows in set (0.00 sec)

Implement the syntax we discussed, in the beginning, to maintain order in MySQL IN query. The query is as follows −

mysql> select *from OrderInDemo OD where OD.Id in(10,1,3)
   −> order by field(OD.Id,10,1,3);

The following is the output that displays the results ordered in the sequence provided in the query −

+------+---------+------+
| Id   | Name    | Age  |
+------+---------+------+
|   10 | Carol   |   19 |
|    1 | John    |   26 |
|    3 | Johnson |   25 |
+------+---------+------+
3 rows in set (0.00 sec)

Vrundesha Joshi

Updated on: 29-Jun-2020

524 Views

Kickstart Your Career

Get certified by completing the course

Get Started