I have table like this
with t as (
select 'USD' FROM_CURRENCY ,
'INR' TO_CURRENCY ,
to_date('01-JAN-2021', 'DD-MON-YYYY') FROM_CONVERSION_DATE,
to_date('01-JAN-2021', 'DD-MON-YYYY') TO_CONVERSION_DATE,
81.5 CONVERSION_RATE
from dual
union all
select 'INR' FROM_CURRENCY ,
'USD' TO_CURRENCY ,
to_date('01-JAN-2021', 'DD-MON-YYYY') FROM_CONVERSION_DATE,
to_date('01-JAN-2021', 'DD-MON-YYYY') TO_CONVERSION_DATE,
0.0122 CONVERSION_RATE
from dual
union all
select 'SGD' FROM_CURRENCY ,
'INR' TO_CURRENCY ,
to_date('01-JAN-2021', 'DD-MON-YYYY') FROM_CONVERSION_DATE,
to_date('01-JAN-2021', 'DD-MON-YYYY') TO_CONVERSION_DATE,
48 CONVERSION_RATE
from dual
union all
select 'USD' FROM_CURRENCY ,
'INR' TO_CURRENCY ,
to_date('02-JAN-2021', 'DD-MON-YYYY') FROM_CONVERSION_DATE,
to_date('02-JAN-2021', 'DD-MON-YYYY') TO_CONVERSION_DATE,
82.5 CONVERSION_RATE
from dual
union all
select 'INR' FROM_CURRENCY ,
'USD' TO_CURRENCY ,
to_date('02-JAN-2021', 'DD-MON-YYYY') FROM_CONVERSION_DATE,
to_date('02-JAN-2021', 'DD-MON-YYYY') TO_CONVERSION_DATE,
0.0121 CONVERSION_RATE
from dual)
select * from t
;
I would like to suppress the inverse conversion rate of a currency and need the unique records.
From the above 5 reocrds , expected Output is only three reocords.
FROM CURENCY TO_CURRENCY FROM_DATE TO_DATE CONVERSION_RATE
USD INR 01-Jan-2021 01-Jan-2021 81.5
SGD INR 01-Jan-2021 01-Jan-2021 48
USD INR 02-Jan-2021 02-Jan-2021 82.5