Get records in a certain order using MySQL?

MySQLMySQLi Database

You can use ORDER BY IF() to get records in a certain order. Let us first create a table:

mysql> create table DemoTable
(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   FirstName varchar(20),
   Branch varchar(20)
);
Query OK, 0 rows affected (1.96 sec)

Following is the query to insert some records in the table using insert command:

mysql> insert into DemoTable(FirstName,Branch) values('John','CS');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable(FirstName,Branch) values('Carol','ME');
Query OK, 1 row affected (0.25 sec)
mysql> insert into DemoTable(FirstName,Branch) values('David','ME');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable(FirstName,Branch) values('Bob','CE');
Query OK, 1 row affected (0.35 sec)
mysql> insert into DemoTable(FirstName,Branch) values('Robert','EE');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable(FirstName,Branch) values('Chris','EEE');
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable(FirstName,Branch) values('James','ME');
Query OK, 1 row affected (0.56 sec)

Following is the query to display records from the table using select command:

mysql> select *from DemoTable;

This will produce the following output:

+----+-----------+--------+
| Id | FirstName | Branch |
+----+-----------+--------+
|  1 | John      | CS     |
|  2 | Carol     | ME     |
|  3 | David     | ME     |
|  4 | Bob       | CE     |
|  5 | Robert    | EE     |
|  6 | Chris     | EEE    |
|  7 | James     | ME     |
+----+-----------+--------+
7 rows in set (0.00 sec)

Following is the query to get records in a certain order using MySQL. The order we have set to begin with Branch ‘ME’:

mysql> select *from DemoTable order by if(Branch='ME',1,0)DESC,Branch;

This will produce the following output:

+----+-----------+--------+
| Id | FirstName | Branch |
+----+-----------+--------+
|  2 | Carol     | ME     |
|  3 | David     | ME     |
|  7 | James     | ME     |
|  4 | Bob       | CE     |
|  1 | John      | CS     |
|  5 | Robert    | EE     |
|  6 | Chris     | EEE    |
+----+-----------+--------+
7 rows in set (0.00 sec)
raja
Published on 29-Apr-2019 14:22:55
Advertisements