SQL Server 2017

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

create table tbl (id int, parent varchar(10), Date datetime);
insert into tbl values
(1, 'Alex', '2018-10-01'),
(1, 'TIM', '2012-11-07'),
(3, 'Cath', NULL),
(3, 'Dog', null);
4 rows affected

select id, Parent, Date From tbl T1
Where coalesce(Date, '') = (select Max(coalesce(Date, '')) from tbl T2 where T1.id= T2.id )


id
Parent
Date


1
Alex
2018-10-01 00:00:00.000

3
Cath
null

3
Dog
null


with ct as
(
    select id, Parent, Date,
           row_number() over (partition by id order by id, date desc) rn
    from   tbl
)
select id, Parent, Date
from   ct
where  rn = 1
order by id


id
Parent
Date


1
Alex
2018-10-01 00:00:00.000

3
Cath
null