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 |