Select single value from same columns based on condition

I have below table and using oracle sql

table name : TestCaseStatus

TestName  Status   TimeStamp
ABC       Passed   11.10AM (Same Date)
ABC       Failed   11.00 AM 
ABC       Failed   10.50 AM
EFG       Passed   11.00AM
123       Failed    11.10 AM
123       Passed    11.00 AM

Result

TestName  Status
ABC       Passed_On_ReRun
123       Failed
EFG       Passed

Question : Need query to get it. I have Tried MAX but not working

Answer

Assuming these are the only three conditions, you can use conditional aggregation:

select testname,
       (case when max(timestamp) = max(case when status = 'Failed' then timestamp end)
             then 'Failed'
             when max(timestamp) = max(case when status = 'Passed' then timestamp end) and
                  sum(case when status = 'Failed' then 1 else 0 end) > 0
             then 'Passed_On_Rerun'
             when max(timestamp) = max(case when status = 'Passed' then timestamp end)
             then 'Passed'
             else '???'
         end)
from TestCaseStatus tcs
group by testname;

In Oracle, you can simplify this to:

select testname,
       (case when max(status) keep (dense_rank first order by timestamp desc) = 'Passed' and
                  sum(case when status = 'Failed' then 1 else 0 end) > 0
             then 'Passed_On_Rerun'
             else max(status) keep (dense_rank first order by timestamp desc)
         end)
from TestCaseStatus tcs
group by testname;

Here is a db<>fiddle.

The keep syntax is getting the last value for the status based on the timestamp.