CODEWITHSUNDEEP
mysql
Pavel K
Pavel K

Reputation: 235

MySQL - remove duplicates only when NOT NULL, using GROUP by two fields

I want to remove duplicates from my table using GROUP BY clause but I want do it only when two fields are not null. If any field is null, then group by shouldn't remove this record.

Example:

| ID | IDEK |
|  1 | null |
|null|  1   |
|null|  1   |
|null| null |
|665 | 22   |
|665 | 22   |

and now, when I use this query:

select * from test group by id, idek

I want to get this result:

| ID | IDEK |
|  1 | null | // HERE IS NULL SO IT SHOULD BE PRINTED ALWAYS
|null|  1   | // HERE IS NULL SO IT SHOULD BE PRINTED ALWAYS
|null|  1   | // HERE IS NULL SO IT SHOULD BE PRINTED ALWAYS
|null| null | // HERE IS NULL SO IT SHOULD BE PRINTED ALWAYS
|665 | 22   |

Is it possible to do it? Here is live-example how it's work now: http://www.sqlfiddle.com/#!9/070218/1

Thanks.

Upvotes: 1

Views: 462

Answers (2)

supravi
supravi

Reputation: 59

You can do the following:

select * from test
where id IS NULL or idek IS NULL 
group by id, idek

Upvotes: 0

Nishant Gupta
Nishant Gupta

Reputation: 3656

Here is the Solution to your problem:

SELECT id,idek 
FROM test
WHERE id IS NULL OR idek IS NULL
UNION ALL
SELECT id,idek 
FROM test
WHERE id IS NOT NULL AND idek IS NOT NULL
GROUP BY id,idek

Demo Link:

http://www.sqlfiddle.com/#!9/070218/8

Upvotes: 2

Related Questions