I'm struggling with a subquery (Oracle 11). Example below is 'invented' for the purpose of the question.
SELECT TM.TMID AS TEAM_ID
,(SELECT FIRST_NAME || ' ' || LAST_NAME
FROM PLAYER PL
WHERE PL.TMID=**TM.TMID**
ORDER BY AGE) AS OLDEST_PLAYER_NAME
FROM TEAM **TM**
Again, this is an invented example. Let's not get into any discussions about naming conventions, normalization or age calculations.
The issue at hand is that the correlated query returns multiple results and I need to pick the first one. If I wrap that correlated query inside another one to do a select .... where rownum<=1, I will break the alias reference.
Please work with my question! When trying to help, please state how I can limit results of the subquery above or state that it cannot be done. Do not rewrite this query into some joins because I would not be able to validate that your solution works. If subquery above cannot be limited to a single row, I will evolve complete query into joins myself. This question is strictly about limiting results without breaking the alias reference.
The constraints on answering exclude joins and to continue with the correlated subquery in the select clause.
The options I can think of are
MAX() or MIN()row_number () as shown elsewhereE.g
( Select MAX(name) from tablex x
where x.id = outer.id )
for a limit of 1 you can do something like the following:
select
customer_id
, (select cust_last_name
from demo_customers c
where c.customer_id = o.customer_id
ORDER BY c.cust_state desc
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
) name
from demo_orders o
But: in that example there is simply no good reason not to use a join instead which would be far more efficient.
select
o.customer_id
, c.cust_last_name
from demo_orders o
inner join demo_customers c
on c.customer_id = o.customer_id