Syntax error Maintain the custom order of the IDs passed in MySQL

Maintain the custom order of the IDs passed in MySQL

To maintain the custom order of IDs, use ORDER BY CASE statement. Let us first create a table −

mysql> create table DemoTable1550
   -> (
   -> Id int,
   -> Name varchar(20)
   -> );
Query OK, 0 rows affected (0.61 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1550 values(101,'Chris');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable1550 values(110,'Bob');
Query OK, 1 row affected (0.29 sec)
mysql> insert into DemoTable1550 values(105,'Carol');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1550 values(109,'Mike');
Query OK, 1 row affected (0.17 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1550;

This will produce the following output −

+------+-------+
| Id   | Name  |
+------+-------+
|  101 | Chris |
|  110 | Bob   |
|  105 | Carol |
|  109 | Mike  |
+------+-------+
4 rows in set (0.00 sec)

Here is the query to maintain the order of the IDs passed −

mysql> select * from DemoTable1550
   -> where Id IN(109,101,110)
   -> order by case Id
   -> when 109 then 1001
   -> when 101 then 1002
   -> when 110 then 1003
   -> end;

This will produce the following output −

+------+-------+
| Id   | Name  |
+------+-------+
|  109 | Mike  |
|  101 | Chris |
|  110 | Bob   |
+------+-------+
3 rows in set (0.00 sec)
Updated on: 2019-12-12T06:23:51+05:30

237 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements