How to order records by a column in MySQL and place empty records at the end?

To get order by column and place empty records at the end, use ORDER By and “is null” from MySQL. The syntax is as follows −

select *from yourTableName order by if(yourColumName = ’ ’ or yourColumName is null,1,0),yourColumnName;

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

mysql> create table emptyCellsAtEnd
   −> (
   −> ProductId varchar(100)
   −> );
Query OK, 0 rows affected (0.65 sec)

Insert some records in the table using insert command. Some of these records are empty. The query is as follows −

mysql> insert into emptyCellsAtEnd values('');
Query OK, 1 row affected (0.23 sec)

mysql> insert into emptyCellsAtEnd values('P-1');
Query OK, 1 row affected (0.21 sec)

mysql> insert into emptyCellsAtEnd values('P-2');
Query OK, 1 row affected (0.14 sec)

mysql> insert into emptyCellsAtEnd values('');
Query OK, 1 row affected (0.15 sec)

mysql> insert into emptyCellsAtEnd values('P-3');
Query OK, 1 row affected (0.18 sec)

mysql> insert into emptyCellsAtEnd values('P-4');
Query OK, 1 row affected (0.09 sec)

mysql> insert into emptyCellsAtEnd values('P-9');
Query OK, 1 row affected (0.11 sec)

mysql> insert into emptyCellsAtEnd values('');
Query OK, 1 row affected (0.15 sec)

mysql> insert into emptyCellsAtEnd values('P-8');
Query OK, 1 row affected (0.17 sec)

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

mysql> select *from emptyCellsAtEnd;

The following is the output −

+-----------+
| ProductId |
+-----------+
|           |  
| P-1       |
| P-2       |
|           |
| P-3       |
| P-4       |
| P-9       |
|           |
| P-8       |
+-----------+
9 rows in set (0.00 sec)

Now you can implement the query we discussed in the beginning for placing empty cells at last. The query to place empty cells at the end is as follows −

mysql> select *from emptyCellsAtEnd
−> order by if(ProductId = '' or ProductId is null,1,0),ProductId;

The following is the output −

+-----------+
| ProductId |
+-----------+
| P-1       |
| P-2       |
| P-3       |
| P-4       |
| P-8       |
| P-9       |
|           |
|           |
|           |
+-----------+
9 rows in set (0.00 sec)

Anvi Jain

Published on 16-Jan-2019 08:08:03

Previous Page Print Page