SQL Server Migration Apply Query to Oracle
Which should be equivalent to below Sql server query in Oracle:
select dd.dname, e.ename
from emp e
outer apply
(select top 1 dname from dept d where d.did=e.did order by bdate) dd
Note that the actual request is very different, but the concept is the same. Please forgive me for any syntax error in the above request.
I tried to execute Oracle query:
select dd.dname, e.ename
from emp e
left join
(select * from
(select dname from dept d where d.did=e.did order by bdate)
where rownum=1) dd
But this results in below error:
Error at Command Line:6 Column:18
Error report:
SQL Error: ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:
+3
Vikas Sharma
source
to share
2 answers
You can order the lines first and then take the first lines:
select e.ename, d.dname
from emp e
left join (
select dept.dname, dept.did, row_number()
over (partition by did order by bdate) rn from dept) d
on e.did = d.did and d.rn=1
SQLFiddle
0
Ponder stibbons
source
to share
Inclusion of a sentence after merging. So, I convert the request to a group on demand and it works:
select dd.dname, e.ename
from emp e
left join
(select min(bdate), dname, did from dept d group by dname, did)dd
on dd.did=e.did
+1
Vikas Sharma
source
to share