grouping but ignore if blank

I'm trying to group (or at least make uniq开发者_运维百科ue) a column m_group_name unless the group is empty. This is to basically see what doesn't yet have a group (and could then be edited to be placed into one) while being able to see the existing groups.

For example;

id  |  m_group_name
---------------------
 1  | Red
 2  | Blue
 3  | 
 4  | Red
 5  | 

Which, ideally, would result in one red, one blue, and 2 blanks.

I've tried making the blanks NULL and giving it a UUID value (after adapting something similar found here on Stackoverflow), but it seems to make every NULL the same value.

SELECT *, IFNULL(m_group_name,UUID()) AS m_group FROM m GROUP BY m_group

Ideally I'd rather not use NULL to be perfectly honest

EDIT: Realised that 'm_group_name' was incorrectly labelled as 'group' in the example - corrected.

That approach will not work because the UUID() function will return a single value per query, rather than a separate value for each row.

You can use the primary key:

SELECT *, IFNULL(m_group_name,id) AS m_group FROM m GROUP BY m_group

Or you can use a different function, such as RAND(), to generate a separate value for each row. You can apply a hash function like MD5() to it if you want a string value:

SELECT *, IFNULL(m_group_name,md5(rand())) AS m_group FROM m GROUP BY m_group

SELECT * FROM m WHERE LENGTH(Group) IN (0, NULL) *LENGTH(NULL) will return NULL. LENGTH('') will return 0.

am I right that you want to see ALL the rows that DON'T yet have a Group?

EDIT: Okay, i read it carefully. How about this:

 SELECT * FROM m WHERE LENGTH(Group) NOT IN (0, NULL) GROUP BY Group
 UNION ALL
 SELECT * FROM m WHERE LENGTH(Group) IN (0, NULL)