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
ORDER BY AGE) AS OLDEST_PLAYER_NAME
FROM TEAM **TM**
select .... where rownum<=1
The constraints on answering exclude joins and to continue with the correlated subquery in the select clause.
The options I can think of are
row_number ()as shown elsewhere
( 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