Syntax error Select items based on value first, then order on the basis of date for rest of the records in MySQL

Select items based on value first, then order on the basis of date for rest of the records in MySQL

Use ORDER BY to fix a record and then display

select * from yourTableName
order by yourColumnName1=yourValue desc,yourColumnName2;

Let us first create a table −

mysql> create table DemoTable1932
   (
   UserName varchar(20),
   ShippingDate date
   );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1932 values('Chris','2018-10-12');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1932 values('David','2019-04-11');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1932 values('Mike','2016-12-04');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1932 values('Carol','2017-12-26');
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1932;

This will produce the following output −

+----------+--------------+
| UserName | ShippingDate |
+----------+--------------+
| Chris    | 2018-10-12   |
| David    | 2019-04-11   |
| Mike     | 2016-12-04   |
| Carol    | 2017-12-26   |
+----------+--------------+
4 rows in set (0.00 sec)

Here is the query to select items based on value first and then order on the basis of date −

mysql> select * from DemoTable1932
    order by UserName='David' desc,ShippingDate;

This will produce the following output −

+----------+--------------+
| UserName | ShippingDate |
+----------+--------------+
| David    | 2019-04-11   |
| Mike     | 2016-12-04   |
| Carol    | 2017-12-26   |
| Chris    | 2018-10-12   |
+----------+--------------+
4 rows in set (0.00 sec)
Updated on: 2019-12-30T07:42:25+05:30

368 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements