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

Updated on 30-Jul-2019 22:30:24

• Related Questions & Answers
• How to Order by date in MySQL but place empty dates in the end?
• How to place number 0 from a column at the end maintaining the ascending search order in MySQL?
• How to place a thousand separator in MySQL records?
• Order a column in MySQL with IP Address records?
• How to ORDER BY DESC and display the first 3 records in MySQL?
• ORDER BY records in MySQL based on a condition
• How to order last 5 records by ID in MySQL
• Implement ORDER BY in MySQL to order records in human readable format?
• Order by a single field and display rest of the records in the same order with MySQL
• Display the count of duplicate records from a column in MySQL and order the result
• Order records and delete n rows in MySQL
• Order MySQL records randomly and display name in Ascending order
• How to order records and fetch some using MySQL LIMIT?
• MySQL query to ORDER BY records on the basis of modulus result
• Get records in a certain order using MySQL?