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

Question

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?

Michael Fredrickson · Accepted Answer · 2012-03-28 17:53:11Z

5

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.

edited Mar 28, 2012 at 17:53

answered Mar 28, 2012 at 17:43

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gregn3 Over a year ago

I need all the v1-s that are on the rows with NULL values, but for the other rows it doesn't matter which of the v1-s remain. That table a comes from another select statement that has LEFT JOIN-s, which produces the NULL-s. (The v1-s are ID values...)

Michael Fredrickson Over a year ago

@gregn3 OK, makes sense to me now... I've updated the answer accordingly.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related