Source data :

* I want to get rows in yellow highlight.

Query :

select agent_id, status, created_date,
  row_number() over(partition by agent_id order by created_date desc) row_num
from test_over

Result

Query :

select agent_id, status, created_date from (
     select agent_id, status, created_date,
         row_number() over(partition by agent_id order by created_date desc) row_num
     from test_over)
where row_num = 1

Result :

Reference :

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm