CODEWITHSUNDEEP
sqlsql-server-2008
WWaldo
WWaldo

Reputation: 223

Getting the Next record when joining to table SQL Server

I'm wanting to join a table that contains co-borrower information to a primary borrower information. The data seems to be inaccurate at points. What I'm seeing is that sometimes the Primary Borrower is also a co-borrower. This shouldn't be happening.

I want to select the next co-borrower if the primary and secondary social Security # match.

This is what I basically have have:

Select a.PrimaryBorrower
,a.Accountnumber
,a.PriamrySSN
,b.SecondarySSN
from  Primarytable A
Left Join Secondarytable B
 on a.accountnumber = B.accountnumber and 
 b.secondaryssn != A.primarySSN

I know this won't work, but i'm not entirely sure how i should go about doing this. 

Primaryssn | AccountNumber | Primaryborrower 
  xxxx1       123               AB
  XXXX2       134               BC
SecondarySSN | Accountnumber | SeondaryBorrower 
  xxxx1         123              AB
  xxxx3         123              CB
  xxxx5         134              RC
  XXXX9         123              ZB

and what i want to get as a result is:

 PrimaryBorrower | Accountnumber |PrimarySSN | SecondarySSN
     AB                 123          xxxx1      xxxx3
     BC                 134          XXXX2      XXXX5

Upvotes: 1

Views: 50

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135809

I think you're close, just a small tweak to arbitrarily choose the secondary borrower with the lowest SSN.

WITH cteRowNums AS (
    Select a.PrimaryBorrower
          ,a.Accountnumber
          ,a.PriamrySSN
          ,b.SecondarySSN
          ,ROW_NUMBER() OVER(PARTITION BY a.Accountnumber ORDER BY b.SecondarySSN) AS RowNum
        from  Primarytable A
            Left Join Secondarytable B
                on a.accountnumber = B.accountnumber 
                    and  b.secondaryssn != A.primarySSN 
)
SELECT PrimaryBorrower
      ,Accountnumber
      ,PriamrySSN
      ,SecondarySSN
    FROM cteRowNums
    WHERE RowNum = 1;

Upvotes: 1

Related Questions