CODEWITHSUNDEEP
sqloracle-database
Skn
Skn

Reputation: 101

Correlated subqueries, max

I have a complex query to extract data based on specific versions. I have a main subquery to specify my criteria to limit data and identify the max version of given record. Now, I have multiple tables, subqueries that I need to join to extract all the data I need, all the data I extract using the subqueries and other tables need to have the version# <= version in the main query. I have a sample query listed below:

select 
    a.id, b.item, c.value, n.notes 
from
    (select 
         id, max(version) mv over partition by id 
     from 
         t1 
     where 
         trunc(date) = '25-Oct-2017') a,
    (select 
         id, version bv, item 
     from 
         t2) b,
    (select 
         id, version cv, value 
     from 
         t3) c,
    (select 
         id, version nv, notes 
     from 
         t8) n
where
    b.id(+) = a.id
    and b.bv <= a.mv
    and c.id(+) = a.id
    and c.cv <= a.mv
    and n.id(+) = a.id
    and n.nv <= a.mv

My subqueries b, c, n etc, sometimes return multiple versions for a record, how do I get a max of the version in that scenario, however, the max may not be > a.mv

P.S: I know I am supposed to use the new join syntax, I am still learning to use that

Upvotes: 0

Views: 557

Answers (1)

user5683823
user5683823

Reputation:

OK, so after you fix the syntax (to have a proper outer join so that all the ID's from a will be present in the final output even if the ID is not found in the other subqueries), you now have many rows per ID, but with a.mv being the max version per id in t1.

That is good. Now, you need to group by ID, and select the item with the highest value from b (after you already filtered by `version <= a.mv'), etc.

So this sounds like an aggregate query (aggregate after the join, grouping by ID), but the problem is that you don't want the MAX(VERSION) from the other subqueries. You also don't want MAX(ITEM) or MAX(VALUE), etc. Rather, you want the item from the MAX(VERSION), the value from the MAX(VERSION) (in a different subquery), etc.

The condition that the versions from b, c, n is already in your where clause (after you fix the outer-query issue I pointed out).

So, all you need is an aggregate function that for some reason many developers are not familiar with, even though it is in the SQL Standard (and therefore it exists in most database products, not just in Oracle): the FIRST/LAST aggregate function.

select a.id, max(b.item)  keep (dense_rank LAST order by b.version) as item,
             max(c.value) keep (dense_rank LAST order by c.version) as version, etc.
from   your outer-join of four subqueries
where  (...)
group by a.id
;

Upvotes: 1

Related Questions