The key word here is FIRST. You can use analytic function FIRST_VALUE
or aggregate construct FIRST
.
For FIRST
or LAST
the performance is never worse and frequently better than the equivalent FIRST_VALUE
or LAST_VALUE
construct because we don’t have a superfluous window sort and as a consequence a lower execution cost:
select table_A.id, table_A.name, firstFromB.city
from table_A
join (
select table_B.id2, max(table_B.city) keep (dense_rank first order by table_B.city) city
from table_b
group by table_B.id2
) firstFromB on firstFromB.id2 = table_A.id
where 1=1 /* some conditions here */
;
Since 12c introduced operator LATERAL
, as well as CROSS/OUTER APPLY
joins, make it possible to use a correlated subquery on right side of JOIN
clause:
select table_A.id, table_A.name, firstFromB.city
from table_A
cross apply (
select max(table_B.city) keep (dense_rank first order by table_B.city) city
from table_b
where table_B.id2 = table_A.id
) firstFromB
where 1=1 /* some conditions here */
;