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
How to specify exact order with WHERE `id` IN (…) in MySql?
To specify exact order with where id IN, you need to use find_in_set() function.
The syntax is as follows
SELECT *FROM yourTableName WHERE yourColumnName IN (yourValue1,yourValue2,yourValue3,....N) ORDER BY FIND_IN_SET(yourColumnName , ‘yourValue1,yourValue2,yourValue3,....N’');
Let us first create a table
mysql> create table FindInSetDemo - > ( - > Id int, - > Name varchar(20), - > Age int - > ); Query OK, 0 rows affected (0.54 sec)
Insert some records in the table using insert command.
The query is as follows
mysql> insert into FindInSetDemo values(10,'John',23); Query OK, 1 row affected (0.20 sec) mysql> insert into FindInSetDemo values(1,'Carol',21); Query OK, 1 row affected (0.17 sec) mysql> insert into FindInSetDemo values(4,'Bob',25); Query OK, 1 row affected (0.19 sec) mysql> insert into FindInSetDemo values(6,'Sam',26); Query OK, 1 row affected (0.15 sec) mysql> insert into FindInSetDemo values(7,'Maxwell',29); Query OK, 1 row affected (0.18 sec) mysql> insert into FindInSetDemo values(8,'Mike',22); Query OK, 1 row affected (0.13 sec) mysql> insert into FindInSetDemo values(2,'David',27); Query OK, 1 row affected (0.20 sec) mysql> insert into FindInSetDemo values(3,'James',20); Query OK, 1 row affected (0.20 sec)
Display all records from the table using select statement.
The query is as follows
mysql> select *from FindInSetDemo;
The following is the output
+------+---------+------+ | Id | Name | Age | +------+---------+------+ | 10 | John | 23 | | 1 | Carol | 21 | | 4 | Bob | 25 | | 6 | Sam | 26 | | 7 | Maxwell | 29 | | 8 | Mike | 22 | | 2 | David | 27 | | 3 | James | 20 | +------+---------+------+ 8 rows in set (0.00 sec)
Here is the query to specify exact order with where id IN()
mysql> select *from FindInSetDemo - > where Id IN (1,4,6,7) - > order by FIND_IN_SET(Id, '1,4,6,7');
The following is the output
+------+---------+------+ | Id | Name | Age | +------+---------+------+ | 1 | Carol | 21 | | 4 | Bob | 25 | | 6 | Sam | 26 | | 7 | Maxwell | 29 | +------+---------+------+ 4 rows in set (0.00 sec)
Updated on: 30-Jul-2019
487 Views
Advertisements