Order by in subquery and alias
I have a problem with an order by in oracle query.
select KEY, B, C, (select D from TABLE1 a where a.KEY = b.KEY and a.DATE<
b.DATE order BY a.DATE and rownum =1 )
FROMSTATUS from TABLE2 b
I knew the “order by” is not working in the subquery. I modify my query as:
select KEY, B, C, (select * from (select D from TABLE1 a where a.KEY =
b.KEY and a.DATE< b.DATE order by DATE) where rownum = 1)
FROM STATUS from TABLE2 b
But in this way, the B.KEY and B.DATE has not resolved by oracle
I need to select only a 1 value from TABLE2 and the value is the first previous a.DATE
TABLE1
KEY DATE A B C
1 01/31/2000 1 2 3
2 02/25/2000 X Y Z
TABLE2
KEY DATE D
1 01/30/2000 1
1 01/27/2000 2
1 01/25/2000 2
2 02/20/2000 4
2 02/13/2000 1
I want this result:
TABLE1.KEY TABLE1.DATE TABLE1.A TABLE1.B TABLE1.C TABLE2.DATE TABLE2.D
1 01/31/2000 1 2 3 01/30/2000 1
2 02/25/2000 X Y Z 02/20/2000 4
Rony cortze
In Oracle you can use KEEP LAST for this:
select
key,
b,
c,
(
select max(d) keep (dense_rank last order by t2.date)
from table2 t2
where t2.key = t1.key and t2.date < t1.date
) as fromstatus
from table1 t1;
As of Oracle 12c you can also use FETCH FIRST ROW:
select
key,
b,
c,
(
select d
from table2 t2
where t2.key = t1.key and t2.date < t1.date
order by t2.date desc
fetch first row only
) as fromstatus
from table1 t1;
or, moving the subquery to the FROM clause:
select
t1.key,
t1.b,
t1.c,
first_t2.d as fromstatus
from table1 t1
outer apply
(
select d
from table2 t2
where t2.key = t1.key and t2.date < t1.date
order by t2.date desc
fetch first row only
) first_t2;
Gurpreet Singh Padam
row_number() after union will get your output.
select tFinal.DATE, tFinal.KEY
from (select row_number() over (partition by KEY order by t1.T, t1.DATE desc) as rn, t1.DATE, t1.KEY
from
(select DATE, KEY, ‘t1’ as T from TABLE1
union all
select DATE, KEY, ‘t2’ as T from TABLE2) t1) tFinal
Where rn = 2