Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Sort by order of values in a MySQL select statement IN clause?
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)
Updated on: 30-Jul-2019
87 Views
Advertisements