
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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)
- Related Articles
- How to Order by date in MySQL but place empty dates in the end?
- Order a column in MySQL with IP Address records?
- How to order last 5 records by ID in MySQL
- How to place number 0 from a column at the end maintaining the ascending search order in MySQL?
- How to ORDER BY DESC and display the first 3 records in MySQL?
- How to place a thousand separator in MySQL records?
- ORDER BY records in MySQL based on a condition
- Implement ORDER BY in MySQL to order records in human readable format?
- Display the count of duplicate records from a column in MySQL and order the result
- How to order records and fetch some using MySQL LIMIT?
- Order by a single field and display rest of the records in the same order with MySQL
- Order records and delete n rows in MySQL
- Order MySQL records randomly and display name in Ascending order
- Get records in a certain order using MySQL?
- MySQL query to ORDER BY records on the basis of modulus result

Advertisements