MySQL Order By date column and integer column, but specify ordering rules of integer column? Is it possible?

MySQLMySQLi Database

You can achieve this with the help of ORDER BY CASE statement. The syntax is as follows −

SELECT *FROM yourTableName ORDER BY CASE yourIntegerColumnName1 WHEN 2 THEN 1 ELSE 0 END DESC ,yourDateColumnName ASC;

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

mysql> create table OrderByCaseDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> GroupId int,
   -> ArrivalDate date,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (1.57 sec)

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

mysql> insert into OrderByCaseDemo(GroupId,ArrivalDate) values(2,'19/02/06');
Query OK, 1 row affected (0.13 sec)
mysql> insert into OrderByCaseDemo(GroupId,ArrivalDate) values(1,'20/02/06');
Query OK, 1 row affected (0.20 sec)
mysql> insert into OrderByCaseDemo(GroupId,ArrivalDate) values(3,'18/02/06');
Query OK, 1 row affected (0.12 sec)
mysql> insert into OrderByCaseDemo(GroupId,ArrivalDate) values(3,'21/02/06');
Query OK, 1 row affected (0.23 sec)
mysql> insert into OrderByCaseDemo(GroupId,ArrivalDate) values(2,'17/02/06');
Query OK, 1 row affected (0.17 sec)
mysql> insert into OrderByCaseDemo(GroupId,ArrivalDate) values(1,'22/02/06');
Query OK, 1 row affected (0.14 sec)

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

mysql> select *from OrderByCaseDemo;

The following is the output −

+----+---------+-------------+
| Id | GroupId | ArrivalDate |
+----+---------+-------------+
|  1 |       2 | 2019-02-06  |
|  2 |       1 | 2020-02-06  |
|  3 |       3 | 2018-02-06  |
|  4 |       3 | 2021-02-06  |
|  5 |       2 | 2017-02-06  |
|  6 |       1 | 2022-02-06  | 
+----+---------+-------------+
6 rows in set (0.00 sec)

Here is the query to order by date column as well as an integer column −

mysql> select *from OrderByCaseDemo order by case GroupId when 2 then 1 else 0 end desc,ArrivalDate asc;

The following is the output −

+----+---------+-------------+
| Id | GroupId | ArrivalDate |
+----+---------+-------------+
|  5 |       2 | 2017-02-06  |
|  1 |       2 | 2019-02-06  |
|  3 |       3 | 2018-02-06  |
|  2 |       1 | 2020-02-06  |
|  4 |       3 | 2021-02-06  |
|  6 |       1 | 2022-02-06  |
+----+---------+-------------+
6 rows in set (0.00 sec)
raja
Published on 27-Feb-2019 07:47:01
Advertisements