CODEWITHSUNDEEP
sqloracle-databasejoin
tomaytotomato
tomaytotomato

Reputation: 4028

Joining one record to resultset in Oracle SQL

I am trying to join the most recent OLD_VAL from my Audit_table which records changes to columns in my main table called Insurance_State.

The problem with my current query is that it is duplicating returned rows in my result set show every single OLD_VAL a record in Insurance_State has been

        SELECT ins.last_upd, cc.id, cc.claim_date, pol.policy_id, ins.state, adt.old_val, ins.review_date, ins.latest_reason, bll.ou_num
    FROM CUSTOMER_CLAIM cc
    JOIN CUSTOMER_CAR car
    ON cc.id = car.ccid
    JOIN POLICY pol
    ON car.integ_id = pol.integ_id
    JOIN INSURANCE_STATE ins
    ON pol.policy_id = ins.policy_id
    JOIN BILLING_ACCT bll
    ON pol.bill_accnt_id = bll.row_id
    --only want to the most recent record from below table
    JOIN AUDIT_TABLE adt
ON pol.policy_id = hst.policy_id

The output this gives in very simplistic terms is

D --> A
D --> B
D --> C

When in reality I only want the most recent old_val so the output would be

D --> C

How can I adjust my query so it does that.

I tried doing a inner select statement inside the join but It does not like my syntax

Any ideas?

Upvotes: 0

Views: 44

Answers (1)

MartenCatcher
MartenCatcher

Reputation: 2887

You have a little mistake:

JOIN AUDIT_TABLE adt ON pol.policy_id = hst.policy_id

May be so:

JOIN AUDIT_TABLE adt ON pol.policy_id = adt.policy_id

And answer to your question:

join (
    select policy_id, old_val from (  
          select at.policy_id, at.old_val, 
                 rank() over (partition by at.policy_id order by at.old_val desc) as rnk 
          from AUDIT_TABLE at
        ) where rnk = 1
    ) adt on pol.policy_id = adt.policy_id

This should work if the statuses are such names (A, B, C, D, etc.). If it is not, in expression order by at.old_val desc you should use a different field, like a id, or addition time.

Upvotes: 1

Related Questions