SQL Query subquery level

Question

SQL Query subquery level

Navigation

#1 by (1 votes)

2

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.

sql oracle

asked by Nippur Lagash 14.09.2018 в 02:18

source

1 answer

Problem with relations in DATASET obtained from database Use javascript within PHP

score 1 · Answer 1

I was answered this question in SQL Query subquery (second level) and I have an answer in English, the way to do what I request is:

select *
from (select gdr.*,
             max(conversion_rate) over (partition by least(currency_from, currency_to), greatest(currency_from, currency_to) ) as max_cr
      from gl.gl_daily_rates gdr
      where gdr.conversion_date = to_date('20180301','YYYYMMDD') 
     ) gdr
where conversion_rate = max_cr;

Thanks to @ gordon-linoff