How to prevent MySQL GROUP BY from collapsing NULL values into a single row?

Fir this, you can use IFNULL() along with ORDER BY clause. Let us first create a table table −

mysql> create table DemoTable1511
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> FirstName varchar(20)
   -> );
Query OK, 0 rows affected (1.97 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1511(FirstName) values('John');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable1511(FirstName) values('Robert');
Query OK, 1 row affected (0.29 sec)
mysql> insert into DemoTable1511(FirstName) values('Mike');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable1511(FirstName) values('Robert');
Query OK, 1 row affected (1.08 sec)
mysql> insert into DemoTable1511(FirstName) values(NULL);
Query OK, 1 row affected (0.68 sec)
mysql> insert into DemoTable1511(FirstName) values(NULL);
Query OK, 1 row affected (1.91 sec)
mysql> insert into DemoTable1511(FirstName) values('Mike');
Query OK, 1 row affected (0.51 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1511;

This will produce the following output −

+----+-----------+
| Id | FirstName |
+----+-----------+
|  1 | John      |
|  2 | Robert    |
|  3 | Mike      |
|  4 | Robert    |
|  5 | NULL      |
|  6 | NULL      |
|  7 | Mike      |
+----+-----------+
7 rows in set (0.00 sec)

Here is the query to prevent MySQL GROUP BY from collapsing NULL values into a single row −

mysql> select min(Id) as Id,FirstName from DemoTable1511 group by FirstName,ifnull(FirstName,Id) order by Id;

This will produce the following output −

+------+-----------+
| Id   | FirstName |
+------+-----------+
|    1 | John      |
|    2 | Robert    |
|    3 | Mike      |
|    5 | NULL      |
|    6 | NULL      |
+------+-----------+
5 rows in set (0.00 sec)

AmitDiwan

Updated on 11-Dec-2019 06:11:19

• Related Questions & Answers
• How to ORDER BY FIELD with GROUP BY in a single MySQL query?
• Sum values of a single row in MySQL?
• Count NOT NULL values from separate tables in a single MySQL query
• MySQL query to group concat and place data into a single row on the basis of 1 values in corresponding column?
• Get values from all rows and display it a single row separated by comma with MySQL
• MySQL query to order by NULL values
• How to check if any value is Null in a MySQL table single row?
• How to use a single MySQL query to count column values ignoring null?
• Display all the column values in a single row separated by comma in MySQL?
• How can we delete a single row from a MySQL table?
• MySQL group by for separate id without using GROUP BY to remove duplicate column row?
• Ignore NULL values from separate tables in a single MySQL query and display count of NOT NULL records
• Get only a single value from a specific MySQL row?
• Write a single MySQL query to return the ID from the corresponding row which is a NOT NULL value
• MySQL LIMIT to select a single row