user6651485 user6651485 - 4 months ago 8
SQL Question

How to limit results of a correlated subquery that uses outer query aliases?

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.

Answer

The constraints on answering exclude joins and to continue with the correlated subquery in the select clause.

The options I can think of are

  1. Use MAX() or MIN()
  2. Use row_number () as shown elsewhere
  3. Use an ORDER BY then: OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

E.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