CODEWITHSUNDEEP
mysqlsqlselectnulldistinct
James Glass
James Glass

Reputation: 4300

Selecting DISTINCT where not null in MySQL

I've got a table that has, for example, ID and Name.

There are multiple rows that contain blank names, but there also can be multiple rows with the same name. I'd like to select all IDs, counting each name only once (DISTINCT), but selecting ALL of the blanks.

The following query naturally only selects ONE blank name. How can I select all the distinct names plus all of the blank names?

SELECT DISTINCT Name, ID
FROM TestTable

Upvotes: 2

Views: 12913

Answers (2)

Oreo
Oreo

Reputation: 553

Only slight improvement I can think of would be:

SELECT DISTINCT Name, ID FROM TestTable where Name <> ''
union all
SELECT '', ID FROM TestTable where Name IS NULL OR Name = ''

Upvotes: 1

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171351

SELECT DISTINCT Name, ID FROM TestTable where Name <> ''
union all
SELECT Name, ID FROM TestTable where Name = ''

Upvotes: 7

Related Questions