with t as
(
select 1 obj_id, 'host1' host, 'ip address1' ipaddress , to_date('01-JAN-2018','DD-MON-YYYY') last_modified_date from dual
union all
select 2 , 'host1', 'ip address1' , to_date('02-JAN-2018','DD-MON-YYYY') from dual
union all
select 3 , 'host2', 'ip address2' , to_date('03-JAN-2018','DD-MON-YYYY') from dual
union all
select 4 , 'host3', 'ip address3' , to_date('04-JAN-2018','DD-MON-YYYY') from dual
union all
select 5 , 'host3', 'ip address3' , to_date('05-JAN-2018','DD-MON-YYYY') from dual
union all
select 6 , 'host5', 'ip address5' , to_date('06-JAN-2018','DD-MON-YYYY')from dual
)
select * from (select distinct host, ipaddress , max(last_modified_date) over (partition by host) last_modified_date from t) tbl_1;
o/p
host3 ip address3 05-01-2018
host2 ip address2 03-01-2018
host1 ip address1 02-01-2018
host5 ip address5 06-01-2018
Expected output with obj_id:
4 host3 ip address3 05-01-2018
3 host2 ip address2 03-01-2018
1 host1 ip address1 02-01-2018
6 host5 ip address5 06-01-2018