

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- 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
Advertisements