Please find the below query .it is not returning the row for the below datas
T1.INV_DT_IN DESC NULLS LAST) AS RN1
T1.INV_DT_OUT DESC NULLS LAST) AS RN2
FROM TABLE1 T1
AND T1.PROD_NO =234
Your expeced output took largest INV_DT_IN and largest INV_DT_OUT in different rows.
So, your way might not work.
Another issue might be in that OLAP specifications are applied to the result rows of a SELECT clause.
So, use the returned values of OLAP specifications in WHERE clause of same subselect is usually meaningless.