CODEWITHSUNDEEP
sqloracle-databaseoracle11ggreatest-n-per-group
denisb
denisb

Reputation: 787

Oracle Join Condition with Top 1

I've tried to find a solution to this including the use of the row_number() method within my join, but somehow can't get the desired outcome.

The issue I'm having right now is based on the fact that I have some records from the join table that have multiple instances (1 to many) with the same criteria which is causing duplicates, so even though I thought I could use a min() or max() on a date, the cases where I have the same EffectDT come out twice.

In addition to this, if I have instances where I don't have an "active" record (XPIRDT IS NOT NULL), I need to pull the expired record, so would I use "OR" statement or simply perform another join to get the expired record in the event the first join doesn't produce any record and have a case condition to evaluate the value before display?

So here's an instance of sample data I'm dealing with:

SAMPLE DATA

So the following with the above data sample still produces 2 records, which I can eliminate one by evaluating the expiry date, BUT will present problems if all the records are expired so won't retrieve anything.

 LEFT OUTER JOIN PARTYXREF
      ON MBR_PERSON.NAMEID = PARTYXREF.NAMEID
     AND PARTYXREF.REFTYPE LIKE 'COMM'
 LEFT JOIN (
                SELECT *
                FROM ADDRDATA TMP
                WHERE TMP.ADDRTYPE = '2'
                AND TMP.EFFECTDT = (SELECT MAX(EFFECTDT) FROM ADDRDATA TMP2 WHERE TMP2.ADDRID = TMP.ADDRID)
             )  MBR_ADDR
       ON PARTYXREF.REFKEY = MBR_ADDR.ADDRID

I've also tried using the following within my "JOIN" statement, however I somehow can't make use of the joining key (REFKEY) within where I have the value hardcoded here, so this actually does work, but can't seem to incorporate in the join statement.

SELECT ADDR.*
FROM (SELECT tmp.*, row_number() OVER (ORDER BY XPIRDT DESC) AS SEQNUM
        FROM ADDRDATA tmp
        WHERE tmp.ADDRTYPE = '2'
          AND tmp.ADDRID = 10948448
       ) ADDR
WHERE SEQNUM = 1

I've wasted one hour too many on this, so I need someone else outlook please! :)

Upvotes: 1

Views: 2132

Answers (1)

Hambone
Hambone

Reputation: 16377

I'm still not clear which record, in your example, you wanted, so let me give you some alternatives.

I gathered from your problem statement that you want the latest effective date of those available. Since there are ties involved, you cannot use max() (as you've already stated), and row_number() is the way to go:

with cte as (
  select
    addrid, effectdt, xpirdt,
    row_number() over (partition by addrid order by effectdt desc) as rn
  from addrdata
)
select
  addrid, effectdt, xpirdt
from cte
where rn = 1

The next part was where you lost me with the nulls... If your secondary sort is by expiration date and you want a null value to trump the latest expiration date, then you would order by the expiration date and put nulls first:

with cte as (
  select
    addrid, effectdt, xpirdt,
    row_number() over 
        (partition by addrid order by effectdt desc, xpirdt nulls first) as rn
  from addrdata
)
select
  addrid, effectdt, xpirdt
from cte
where rn = 1

Which means this row is the winner:

10948448    5/14/2015   <null>

If, however, you wanted nulls to be considered only if there are no expiration dates, you can use nulls last (or omit it, as it is the default):

with cte as (
  select
    addrid, effectdt, xpirdt,
    row_number() over
        (partition by addrid order by effectdt desc, xpirdt nulls last) as rn
  from addrdata
)
select
  addrid, effectdt, xpirdt
from cte
where rn = 1

Meaning this guy has won the prize:

10948448    5/14/2015   5/13/2015

Since this uses row_number() you will not lose any rows -- each row is guaranteed to have a row number. It's just that if there are true ties, then it's a toss-up as to which row is chosen. However, your issue with null expiration dates should not cause any problems with this approach.

-- edit 2/13/16 --

I think I'm starting to understand your issue, but I'm not 100% sure. I've incorporated snippets of your code with the left join with my suggestion, and the need to have null expiration dates first, and this is my next crack:

with cte as (
  select
    addrid, effectdt, xpirdt,
    row_number() over 
        (partition by addrid order by effectdt desc, xpirdt nulls first) as rn
  from addrdata
)
select
  cte.addrid, effectdt, xpirdt
from
  mbr_person mb
  left join partyxref px on
    mb.nameid = px.nameid and
    px.reftype = 'COMM'
  left join cte on
    px.refkey = cte.addrid and
    cte.rn = 1

Assuming this doesn't do it:

  1. When you say a null XPIRDT takes precedence -- do you mean even over more recent effective dates, or is it only a tiebreaker for the most recent EFFECTDT? If the latter, then what I have should work. If the former, then we need to switch the order by's in the analytic function
  2. I am totally guessing when it comes to the partyxref and mbr_person tables. If this doesn't cut it, maybe post some sample data and desired output for including those two tables, or fiddle it?

Upvotes: 2

Related Questions