Como converter uma subconsulta Top 1 usando o alias de tabela externa para Oracle?

você executaria a junção primeiro no Oracle:

SELECT a1, a2, b2
  FROM (SELECT a1, a2, b2, 
               row_number() over(PARTITION BY a.a1 ORDER BY b.b2) lfd
           FROM AA a
           LEFT JOIN BB b ON b.b1 = a.a1
          WHERE a2 = 2)
 WHERE lfd = 1

O problema com sua consulta é que atualmente uma subconsulta no Oracle não pode acessar um valor de uma consulta pai com mais de dois níveis de profundidade.

Você também pode usar uma função PL/SQL que contenha o SELECT interno.

Se estes são os resultados que você está procurando:

CODE QTY  FRUIT_PRICE  FRUIT_NAME  DRINK_PRICE   DRINK_NAME  
---- ---- ------------ ----------- ------------- ------------
A    1    2.4          apple       5.4           aperol      
B    1    1.3          banana      4.3           bear        
C    1                                                       

Você pode obtê-lo com isso:

SELECT o.code, o.qty, f.fruit_price, f.fruit_name, d.drink_price, d.drink_name 
FROM want_to_eat o
LEFT JOIN (
   SELECT Row_Number() OVER (PARTITION BY Fruit_Code ORDER BY f.datetime desc) 
        FruitRow
      , fruit_price, fruit_name, fruit_code
   FROM Fruit f
   ) f ON f.fruit_code = o.code AND f.FruitRow = 1
LEFT JOIN (
   SELECT Row_Number() OVER (PARTITION BY Drink_Code ORDER BY d.datetime desc) 
        DrinkRow
      , Drink_price, Drink_name, Drink_code
   FROM Drink d
   ) d ON d.Drink_code = o.code AND d.DrinkRow = 1
WHERE qty = 1;

Aqui vou mostrar alguns resultados que obtive ao usar a abordagem de Vincent Malgrat.

Primeiro, aprendi que, ao usar mais de 1 subconsulta top 1 com base em tabelas ou pedidos diferentes, devo usar a função RANK () e não a função ROW_NUMBER () .

Em segundo lugar, ao usar rank (), há o problema de empates. O top 1 do SQL Server seleciona arbitrariamente uma das linhas com classificação = 1, enquanto o uso de classificação () pode retornar mais de 1 linha.

Eu acho que é um design ruim usar o SQL Server top 1 nesses casos. Para tornar o design correto, algumas restrições exclusivas (por exemplo, índices exclusivos) devem ser encontradas, o que evita essa ambigüidade.

Aqui está um exemplo do SQL Server, se você quiser experimentá-lo sozinho.

A comparação dos planos de execução das duas últimas instruções select abaixo mostra que a abordagem de Vincent Malgrat é melhor do que a primeira solução.

SET NOCOUNT ON

begin try drop table fruit       end try begin catch end catch; 
begin try drop table want_to_eat end try begin catch end catch; 
begin try drop table drink       end try begin catch end catch; 

create table fruit (
 fruit_code char(1),
 fruit_name varchar(20),
 date   datetime,
 fruit_price  money
);
go

create table drink  (
 drink_code char(1),
 drink_name varchar(20),
 date   datetime,
 drink_price  money
);
go
create table want_to_eat (
 code char(1),
 qty  integer,
);
go
insert into want_to_eat values ( 'A', 1);
insert into want_to_eat values ( 'B', 2);
insert into want_to_eat values ( 'B', 1);
insert into want_to_eat values ( 'C', 1);


insert into fruit values ( 'A', 'apple',  '20100101', '2.20');
insert into fruit values ( 'A', 'apple',  '20110101', '2.40');
insert into fruit values ( 'B', 'banana', '20100101', '1.40');
insert into fruit values ( 'B', 'banana', '20110101', '1.30');
insert into fruit values ( 'B', 'banana', '20110101', '1.35');

insert into drink values ( 'A', 'aperol',  '20100101', '5.20');
insert into drink values ( 'A', 'aperol',  '20110101', '5.40');
insert into drink values ( 'B', 'bear',    '20100101', '4.40');
insert into drink values ( 'B', 'bear',    '20110101', '4.30');

create unique index iu_drink on drink(drink_code, date);
-- create unique index iu_fruit on fruit(fruit_code, date); -- Error


Select top 1 fruit_price from fruit where fruit_code = 'A' order by date desc;
Select top 1 fruit_price from fruit where fruit_code = 'B' order by date desc;
Select top 1 fruit_price from fruit where fruit_code = 'C' order by date desc;

SELECT
qty,
(Select top 1 fruit_price from fruit where fruit_code = code order by date desc) fruit_price,
(Select top 1 fruit_name  from fruit where fruit_code = code order by date desc) fruit_name,
(Select top 1 drink_price from drink where drink_code = code order by date desc) drink_price,
(Select top 1 drink_name  from drink where drink_code = code order by date desc) fruit_name
FROM want_to_eat
WHERE qty = 1;

SELECT qty, fruit_price, fruit_name , drink_price,drink_name from (
-- SELECT * FROM (
SELECT
RANK() OVER (PARTITION BY o.CODE ORDER BY f.date desc) f_lfd,
RANK() OVER (PARTITION BY o.CODE ORDER BY d.date desc) d_lfd,
f.date f_date,
code,
qty, 
fruit_price,
fruit_name,
drink_price,
drink_name
from want_to_eat o
left join fruit f on o.code = fruit_code
left join drink d on o.code = drink_code
WHERE qty = 1

) t 
where f_lfd = 1
and d_lfd = 1;

Resposta para Leigh Riffel:

Nas linhas seguintes com id 1 e 2, ambos têm rank () e densa_rank de 1.

create table rank_test (
id int,
grp  int,
val varchar(10)
);

insert into rank_test values (1, 1, 'a');
insert into rank_test values (2, 1, 'a');
insert into rank_test values (3, 1, 'b');
insert into rank_test values (4, 2, 'b');

select * from rank_test;

select r.*,
RANK() OVER (PARTITION BY grp ORDER BY val) rank,
DENSE_RANK() OVER (PARTITION BY grp ORDER BY val) d_rank
from rank_test r
order by id;

Resultado:

id          grp         val        rank                 d_rank
----------- ----------- ---------- -------------------- --------------------
1           1           a          1                    1
2           1           a          1                    1
3           1           b          3                    2
4           2           b          1                    1

Nova resposta para Leigh Riffel:

Aqui um exemplo, não consigo transformar com seu padrão. Tenho uma tabela com artigos e preços e uma segunda tabela com o histórico de variações de preços. Alguma autoridade quer saber o preço antigo, quando o preço mudou.

create table artikel (id int, price int);
create table price_history (id int,price int,v_date datetime);

insert into artikel values (1, 11), (2, 22);
insert into price_history values (1, 11, '20110101'), (2, 20,'20110101');
insert into price_history values (1, 11, '20110201'), (2, 20,'20110201'); -- the true table has more columns, which values might change while price stays the same
insert into price_history values (1, 11, '20110301'), (2, 22,'20110301');

Select id,
       (SELECT TOP 1 price_history.price FROM price_history WHERE price_history.id = artikel.id AND artikel.price <> price_history.price ORDER by v_date DESC ) priceOld
from artikel 
ORDER BY id;

Select artikel.id, y.price priceOld
from    artikel
LEFT JOIN (
   SELECT Row_Number() OVER (PARTITION BY id ORDER BY v_date desc) 
        yRow
      , id, price
   FROM price_history
   ) y ON y.id = artikel.id AND y.yRow = 1 and artikel.price <> y.price
ORDER BY id;   

eu quero pegar

id          priceOld
----------- -----------
1           NULL
2           20

mas a segunda dá

id          priceOld
----------- -----------
1           NULL
2           NULL

A abordagem de Vincent Malgrat produz o resultado correto

Select id, priceOld from (
Select 
    Row_Number() OVER (PARTITION BY a.id ORDER BY v_date desc) vRow, 
    a.id, h.price priceOld
from artikel a
LEFT JOIN
   price_history h ON a.id = h.id and a.price <> h.price
) t 
where vRow = 1   
ORDER BY id;