CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11goracle10g
Kkunnuthara
Kkunnuthara

Reputation: 69

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

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

Upvotes: 1

Views: 72

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

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';

Upvotes: 5

Del
Del

Reputation: 1599

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';

Upvotes: 2

Related Questions