CODEWITHSUNDEEP
mysql
gregn3
gregn3

Reputation: 1774

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

I have a table such as:

table a
+------+-------+
| v1   | v2    |
+------+-------+
|    1 | one   |
|    2 | two   |
|    3 | two   |
|    4 | two   |
|    5 | NULL  |
|    6 | NULL  |
|    7 | three |
|    8 | three |
+------+-------+

I want to group the data in this table by the column 'v2'...

mysql> select * from a group by v2 order by v1;
+----+-------+
| v1 | v2    |
+----+-------+
|  1 | one   |
|  2 | two   |
|  5 | NULL  |
|  7 | three |
+----+-------+

... but leave the NULL values on separate rows. Is it possible to prevent the NULL values from being collapsed into a single row?

Upvotes: 2

Views: 1591

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

This would accomplish what you're asking:

select min(v1) as v1, v2 from a group by v2, ifnull(v2, v1) order by v1;

This would group by v2, unless it is NULL, in which case it would group by v1. Since your two NULL values have two different values for v1, it would split them apart.

Upvotes: 5

Related Questions