Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following SQL Server query

select
    (select top 1 b2 from  BB b where b.b1 = a.a1 order by b2) calc,
    a1,
    a2
from AA a
where a2 = 2;

which I can rewrite using analytic functions

select
    (select b2 from 
    (select 
        row_number() over (order by b2) lfd, 
     b2 from  BB b where b.b1 = a.a1
    ) as t where lfd = 1
    ) calc,
    a1,
    a2
from AA a
where a2 = 2;

but when I convert this to oracle

create table AA ( a1 NUMBER(10), a2 NUMBER(10) );
insert into AA values ( 1, 1);
insert into AA values ( 1, 2);
insert into AA values ( 1, 3);
insert into AA values ( 2, 2);

create table BB ( b1 NUMBER(10), b2 NUMBER(10) );
insert into BB values ( 1, 1);
insert into BB values ( 2, 4);
insert into BB values ( 2, 5);


select * from AA;
select * from BB;


select
    (select b2 from 
    (select 
        row_number() over (order by b2) lfd, 
     b2 from  BB b where b.b1 = a.a1
    )  where lfd = 1
    ) calc,
    a1,
    a2
from AA a
where a2 = 2;

I get the following error

Error at line 5
ORA-00904: "A"."A1": invalid column name
share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

you would perform the join first in 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

The problem with your query is that currently a subquery in Oracle can't access a value from a parent query more than two level deeper.

You could also use a PL/SQL function that would contain the inner SELECT.

share|improve this answer
    
I have more than one column based on top 1 sub-queries in the complete SQL Server query to convert –  bernd_k Mar 31 '11 at 13:45
    
@bernd_k: if the windowing clause (inner table, order) is the same you can return more columns from the inner query. Additional different windowing clauses will need an extra join. –  Vincent Malgrat Mar 31 '11 at 13:52
    
Using functions seems the most practical solution –  bernd_k Mar 31 '11 at 14:42
1  
In some cases the join method will be more performant (mainly because it allows Oracle to work with sets of rows instead of single-row operations). –  Vincent Malgrat Mar 31 '11 at 14:45
1  
This post on Asktom claims that the restriction is part of the ANSI SQL specifications. –  Vincent Malgrat Mar 31 '11 at 14:54
show 3 more comments

Here I will show some results I got while using Vincent Malgrat's approach.

First I learned, that when using more than 1 such top 1 sub query based on different tables or order, I have to use the RANK() function and not the ROW_NUMBER() function.

Second when using rank() there is the problem of ties. SQL Server's top 1 arbitrarily selects one of the rows with rank = 1 while using rank() can return more than 1 row.

I think it is bad design to use SQL Server top 1 in these cases. To make the design correct some unique constraints (e.g. unique indexes ) have to be found, which prevent this ambiguity.

Here is a SQL Server example if you want to try it by yourself.

Comparing the execution plans of the last two select statements below shows that Vincent Malgrat's approach is better than the top 1 solution.

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;

Answer to Leigh Riffel:

In the following rows with id 1 and 2 both have rank() and dense_rank of 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;

Result:

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

New answer to Leigh Riffel:

Here an example, I can't transform with your pattern. I have a table with articles an prices and a second table with the history of price changes. Some authority wants to know the old price, when the price has changed.

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;   

I want to get

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

but the second gives

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

Vincent Malgrat's approch yields the correct result

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;   
share|improve this answer
    
I think the same rows will get rank() = 1 and dense_rank = 1. I'm not interested in rows with higher rank. Top 1 would correspondent to a function which yields 1 only for a single row. –  bernd_k Apr 20 '11 at 19:50
    
I re-read your answer and I think I see the direction you are trying to go now. See my answer. –  Leigh Riffel Apr 21 '11 at 13:55
    
Oh, I didn't know that Vincent Malgrat's solution worked for your other case. I wasn't trying to create a solution for all top(n) queries I was just trying to make sure you had a solution for your specific ones, which it looks like you do now. Thank you for the clear examples. –  Leigh Riffel Apr 22 '11 at 13:18
add comment

If these are the results you are looking for:

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                                                       

You can get it with this:

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;
share|improve this answer
    
There is a small typo: in my SQL Server example the column name is date and it's type is datetime. It helps in 5 of 6 cases of my underlying problem. The remaining case looks like: (SELECT TOP 1 arx.Apo_VK FROM arx WHERE arx.PZN = art.PZN AND arx.Apo_VK <> art.Apo_VK ORDER by Gdat_Preise DESC ) ... from art –  bernd_k Apr 21 '11 at 14:36
    
I had to change from date to datetime since date is reserved in Oracle. I should have mentioned that. Can you show the data you have and the results you would like for this last case? It seems like a simple Row_Number() (ORDER BY Gdat_Preise DESC) and then a where clause looking for 1 would do it. –  Leigh Riffel Apr 21 '11 at 14:47
    
Perhaps enhancing Oracle with top (n) would generate less trouble (LOL). I added the example where your pattern doesn't apply. –  bernd_k Apr 22 '11 at 12:20
    
@bernd_k I'd rather see limit and offset or the similar SQL standard forms. –  Jack Douglas Aug 29 '12 at 14:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.