One option is to use one of analytic functions (such as rank which might be better than row_number in case of multiple rows with the same date, while other column values differ - probably not the case in this situation), sort them per employee by date in descending order and then extract rows that ranked as the highest.
Sample data:
SQL> with employee (id, name, eff_date) as
2 (select 123, 'Duke', date '2022-01-01' from dual union all
3 select 123, 'Duke', date '2023-02-01' from dual union all
4 select 123, 'Duke', date '2022-12-01' from dual union all
5 --
6 select 456, 'Mike', date '2022-01-01' from dual union all
7 select 456, 'Mike', date '2022-12-01' from dual
8 ),
Query:
9 temp as
10 (select id, name, eff_date,
11 rank() over (partition by id order by eff_date desc) rnk
12 from employee
13 )
14 select id, name, eff_date from temp
15 where rnk = 1;
ID NAME EFF_DATE
---------- ---- -----------
123 Duke 01-FEB-2023
456 Mike 01-DEC-2022
SQL>
Option we used before analytic functions were introduced finds max date in a subquery, but that option is now worse than the previous one as it scans the same table (employees) twice.
<snip>
9 select id, name, eff_date
10 from employee a
11 where a.eff_date = (select max(b.eff_date)
12 from employee b
13 where b.id = a.id
14 );
ID NAME EFF_DATE
---------- ---- -----------
123 Duke 01-FEB-2023
456 Mike 01-DEC-2022
SQL>
