MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE people (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    birth_date DATE DEFAULT NULL,
    email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB ;

INSERT INTO people (name, birth_date, email)
VALUES
    ('Gamow', '1904-03-04', 'george.gamow@science.ua'),  -- 1
    ('Gamow', NULL, NULL),  -- 2
    ('Gamow', '1904-03-04', NULL),  -- 3
    ('Gamow', '1904-03-04', 'george-gamow@science.ua'),  -- 4
    ('Gamow', NULL, 'george.gamow@science.ua'),  -- 5
    ('Feynman', '1918-05-11', 'richard.feynman@science.us'),  -- 6
    ('Feynman', '1918-05-11', NULL),  -- 7
    ('Poincaré', '1854-04-29', 'henri.poincare@science.fr')  -- 8
;

Records: 8  Duplicates: 0  Warnings: 0


select * from people;


id
name
birth_date
email


1
Gamow
1904-03-04
george.gamow@science.ua

2
Gamow
null
null

3
Gamow
1904-03-04
null

4
Gamow
1904-03-04
george-gamow@science.ua

5
Gamow
null
george.gamow@science.ua

6
Feynman
1918-05-11
richard.feynman@science.us

7
Feynman
1918-05-11
null

8
Poincaré
1854-04-29
henri.poincare@science.fr


select min(p.id) id,
       p.name
from people p
inner join (select min(id) id,
                   name
            from people
            group by name,email 
          )  x on x.id=p.id
group by p.name,p.birth_date


id
name


1
Gamow

2
Gamow

6
Feynman

8
Poincaré


create table people_new  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    birth_date DATE DEFAULT NULL,
    email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB ;
insert into people_new ( select * 
                         from people 
						 where id in ( select min(p.id) id
                                       from people p
                                       inner join (select min(id) id
                                                from people
                                                group by name,email 
                                                )  x on x.id=p.id
                                    group by p.name,p.birth_date
									)
						)

Records: 4  Duplicates: 0  Warnings: 0


select * from people_new;


id
name
birth_date
email


1
Gamow
1904-03-04
george.gamow@science.ua

2
Gamow
null
null

6
Feynman
1918-05-11
richard.feynman@science.us

8
Poincaré
1854-04-29
henri.poincare@science.fr

id	name	birth_date	email
1	Gamow	1904-03-04	george.gamow@science.ua
2	Gamow	null	null
3	Gamow	1904-03-04	null
4	Gamow	1904-03-04	george-gamow@science.ua
5	Gamow	null	george.gamow@science.ua
6	Feynman	1918-05-11	richard.feynman@science.us
7	Feynman	1918-05-11	null
8	Poincaré	1854-04-29	henri.poincare@science.fr