Oralce Order by DATE field does not work

I have a table MyTAB in Oracle 11gR2 with rows:

F1      F2                          F3
-----------------------------------
x        7/8/2011              100
x        7/27/2011            200
x                                       300
y                                       400
y                                       600

Need O/P:
F1      F2                          F3
-----------------------------------
x        7/27/2011            200
y                                       600

Here F1 is varchar2, F2 is DATE and F3 is number.

I need to get max(F2) i.e. the latest F2 value for a given F1 and the corresponding record from above. If there is at least one non-null F2 value for a given F1, I ignore any null F2 row for that given F1 (in the above case ignore row 3 with F3=300)
And if "all" F2 values are null then  I need to choose the record with max F3.

I tried as in attached code by with date is not coming in ascending order. 7/27/2011 seems lower than 7/8/2011 . Also I cannot choose the largest F3 value when all F2 values are null.



 

select * from 
(SELECT r.*, ROW_NUMBER() OVER(PARTITION BY r.F1 ORDER BY r.F2 ASC) rn                                                                 
from MyTAB r)
where rn = 1

Select allOpen in new window