Oracle 21c

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

--Select the greatest n per group: For a given ASSET_ID, select the inspection that has the latest date_.
with roadinsp (objectid, asset_id, date_) as (
select  1, 1, to_date('2016-04-01','YYYY-MM-DD') from dual union all
select  2, 1, to_date('2019-03-01','YYYY-MM-DD') from dual union all
select  3, 1, to_date('2022-01-01','YYYY-MM-DD') from dual union all
select  4, 2, to_date('2016-04-01','YYYY-MM-DD') from dual union all
select  5, 2, to_date('2022-01-01','YYYY-MM-DD') from dual union all
select  6, 3, to_date('2022-01-01','YYYY-MM-DD') from dual union all
select  7, 3, to_date('2022-03-01','YYYY-MM-DD') from dual union all
select  8, 3, to_date('2018-03-01','YYYY-MM-DD') from dual union all
select  9, 3, to_date('2013-03-01','YYYY-MM-DD') from dual union all
select 10, 3, to_date('2010-06-01','YYYY-MM-DD') from dual union all
select 11, 3, to_date('2022-03-01','YYYY-MM-DD') from dual          
) SELECT * FROM roadinsp WHERE
--SQL expression:
date_ = (select max(subq.date_) from roadinsp subq where roadinsp.asset_id = subq.asset_id)
order by asset_id

OBJECTID	ASSET_ID	DATE_
3	1	01-JAN-22
5	2	01-JAN-22
11	3	01-MAR-22
7	3	01-MAR-22