Iterating over the entire table, and passing all values that satisfies an inner query to an outer query

user_12376206 0
Viewed 57
--Pass each of the pmi value from the below query to an outer query.
select pmi 
from support
where supportid = (select supportid 
                   from support 
                   where pmi = '123'
                   order by datereceived DESC
                   fetch first 1 row only)
  and reason ='XYZ'

I have multiple rows for a particular pmi in my table support. For each set of pmi, I need to find the latest row using the datereceived column, and then check whether that latest row has a reason 'XYZ'.

Only if the reason is 'XYZ' in the latest row, I have to pass the pmi value to an outer query.

It works if I hard code a pmi into the inner query, but I want the inner query to iterate over all the pmi in the table.

Any help would be appreciated.

oracle11g oracle10g plsql oracle sql
3 Answers

You don't need a subquery at all. Simply aggregate by pmi and use KEEP LAST to check whether the last rows' reason is XYZ:

select pmi
from support
group by pmi
having max(reason) keep (dense_rank last order by datereceived) = 'XYZ';

KEEP LAST is Oracle propriatery, though. If you want something closer to your own query (and standard compliant at that), you can use:

select pmi 
from support
where (pmi, datereceived) in
(
  select pmi, max(datereceived)
  from support 
  group by pmi
)
and reason = 'XYZ';
user_22707620

This is what analytical functions are useful for:

SELECT *
FROM (SELECT pmi, 
             supportid, 
             datereceived, 
             reason, 
             MAX(datereceived) OVER (PARTITION BY pmi) AS MAX_DATE
      FROM support) s
WHERE s.datereceived = s.max_date
AND s.reason = 'XYZ';
user_22997600

Throsten has a fine answer. An alternative method that uses standard SQL and aggregation is:

select pmi
from support
group by pmi
having max(datereceived) = max(case when reason = 'XYZ' then datereceived end);

That is, return all pmi where the date on 'XYZ' is the last date.

user_11440350
Related