I have a query that throws level error. It is a table that has the rates of change in two directions Example:
moneda_from | moneda_to | tasa conversion ARS | USD | 0.20 USD | ARS | 20.00
I want to get one of the games, for example, the one with the greatest value
moneda_from | moneda_to | tasa conversion USD | ARS | 20.00
In the table, obviously, there are many combinations of different currencies and the following query would solve the problem, but it does not reach the second level
select * from gl.gl_daily_rates gdr where gdr.conversion_date = to_date('20180301','YYYYMMDD') and gdr.conversion_rate = (select max(total.conversion_rate) from (select gdr2.conversion_rate from gl.gl_daily_rates gdr2 where gdr2.conversion_date = gdr.conversion_date and gdr2.from_currency = gdr.from_currency and gdr2.to_currency = gdr.to_currency union all select gdr3.conversion_rate from gl.gl_daily_rates gdr3 where gdr3.conversion_date = gdr.conversion_date and gdr3.from_currency = gdr.to_currency and gdr3.to_currency = gdr.from_currency ) total);
Does anyone think of how it can be solved? Thanks.