MySQL 8.0

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

create table analyzes_test (id int, code varchar (20),deleted_date datetime)
insert into analyzes_test
values (1,'002','20180701'),
(2,'002','20180702'),
(3,'002','20180703'),
(4,'005','20180718'),
(5,'005','20180701'),
(6,'006','20180710'),
(7,'007','20180703'),
(8,'007',null)

Records: 8  Duplicates: 0  Warnings: 0


select * from analyzes_test


id
code
deleted_date


1
002
2018-07-01 00:00:00

2
002
2018-07-02 00:00:00

3
002
2018-07-03 00:00:00

4
005
2018-07-18 00:00:00

5
005
2018-07-01 00:00:00

6
006
2018-07-10 00:00:00

7
007
2018-07-03 00:00:00

8
007
null


SELECT t.id 
FROM analyzes_test t
JOIN (SELECT code, max(deleted_date) deleted_date
FROM analyzes_test 
WHERE code NOT IN (SELECT code FROM analyzes_test WHERE deleted_date IS null) AND code IS NOT null
GROUP BY code
HAVING count(code)>1 ) x 
ON t.code=x.code AND t.deleted_date=x.deleted_date


id


3

4

id	code	deleted_date
1	002	2018-07-01 00:00:00
2	002	2018-07-02 00:00:00
3	002	2018-07-03 00:00:00
4	005	2018-07-18 00:00:00
5	005	2018-07-01 00:00:00
6	006	2018-07-10 00:00:00
7	007	2018-07-03 00:00:00
8	007	null