Sort by order of values in a MySQL select statement IN clause?

MySQLMySQLi Database

You can use field() function with ORDER BY clause to sort by order of values. The syntax is as follows

SELECT *FROM yourTableName
WHERE yourColumnName IN(Value1,Value2,Value3,.......N);
ORDER BY FIELD(yourColumnName ,Value1,Value2,Value3,.......N);

To understand the above syntax, let us create a table. The query to create a table is as follows

mysql> create table SelectInDemo
   -> (
   -> StudentId int,
   -> StudentName varchar(100),
   -> StudentAge int
   -> );
Query OK, 0 rows affected (1.04 sec)

Insert records in the table using insert command. The query is as follows

mysql> insert into SelectInDemo values(1,'Mike',23);
Query OK, 1 row affected (0.21 sec)

mysql> insert into SelectInDemo values(10,'Bob',21);
Query OK, 1 row affected (0.16 sec)

mysql> insert into SelectInDemo values(11,'Carol',30);
Query OK, 1 row affected (0.25 sec)

mysql> insert into SelectInDemo values(15,'Sam',24);
Query OK, 1 row affected (0.15 sec)

mysql> insert into SelectInDemo values(20,'John',26);
Query OK, 1 row affected (0.09 sec)

mysql> insert into SelectInDemo values(101,'David',27);
Query OK, 1 row affected (0.25 sec)

mysql> insert into SelectInDemo values(96,'Justin',23);
Query OK, 1 row affected (0.27 sec)

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

mysql> select *from SelectInDemo;

The following is the output

+-----------+-------------+------------+
| StudentId | StudentName | StudentAge |
+-----------+-------------+------------+
|         1 | Mike        |         23 |
|        10 | Bob         |         21 |
|        11 | Carol       |         30 |
|        15 | Sam         |         24 |
|        20 | John        |         26 |
|       101 | David       |         27 |
|        96 | Justin      |         23 |
+-----------+-------------+------------+
7 rows in set (0.00 sec)

Here is the query to use IN with SELECT statement in MySQL

mysql> select *from SelectInDemo
   -> where StudentId IN(1,96,101,10,15,11,20)
   -> order by field(StudentId,1,96,101,10,15,11,20);

The following is the output

+-----------+-------------+------------+
| StudentId | StudentName | StudentAge |
+-----------+-------------+------------+
|         1 | Mike        |         23 |
|        96 | Justin      |         23 |
|       101 | David       |         27 |
|        10 | Bob         |         21 |
|        15 | Sam         |         24 |
|        11 | Carol       |         30 |
|        20 | John        |         26 |
+-----------+-------------+------------+
7 rows in set (0.00 sec)

Let us see for another order.

The query is as follows for second order.

mysql> select *from SelectInDemo
   -> where StudentId IN(1,10,11,15,20,101,96)
   -> order by field(StudentId,1,10,11,15,20,101,96);

The following is the outpu

+-----------+-------------+------------+
| StudentId | StudentName | StudentAge |
+-----------+-------------+------------+
|         1 | Mike        |         23 |
|        10 | Bob         |         21 |
|        11 | Carol       |         30 |
|        15 | Sam         |         24 |
|        20 | John        |         26 |
|       101 | David       |         27 |
|        96 | Justin      |         23 |
+-----------+-------------+------------+
7 rows in set (0.00 sec)
raja
Published on 16-Jan-2019 12:22:16
Advertisements