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)
Published on 11-Dec-2019 10:40:55
