SQLite 3.39

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

create table tbl (
  id int,
  id_parent int,
  date date,
  id_status int
);
✓
insert into tbl values
(1, 1, '2024-03-17', 2),
(2, 2, '2020-01-01', 2),
(3, 1, '2023-10-12', 2),
(4, 1, '2023-12-31', 1),
(5, 3, '2020-03-07', 1),
(6, 3, '2018-01-01', 2);
✓
select * from (
  select *, max(date)
  from tbl
  group by id_parent
)
where id_status = 2


id
id_parent
date
id_status
max(date)


1
1
2024-03-17
2
2024-03-17

2
2
2020-01-01
2
2020-01-01