CODEWITHSUNDEEP
sqlsql-serversql-server-2008join
Jason
Jason

Reputation: 23

Join two tables, only use latest value of right table

I am trying to join 2 tables, but only join with the latest record in a group of records.

The left table:

Part

The right table:

Material

The revision number starts at "A" and increases.

I would like to join the 2 tables by PartNum, but only join with the latest record from right table. I have seen other examples on SO but an having a hard time putting it all together.

Edit:

I found out the first revision number is "New", then it increments A,B,... It will never be more than one or two revisions, so I am not worried about going over the sequence. But how do I choose the latest one with 'New' being the first revision number?

Upvotes: 2

Views: 2272

Answers (3)

J Cooper
J Cooper

Reputation: 4998

SQL Server 2005+ as well:

Updated to handle OPs changing requirements

SELECT P.PartNum,
       M.Formula,
       M.RevisionNum
FROM Part AS P
CROSS APPLY (
    SELECT TOP 1 *
    FROM Material AS M
    WHERE M.Partnum = P.PartNum
    ORDER BY CASE WHEN RevisionNum ='New' THEN 2 ELSE 1 END,
             M.RevisionNum DESC
) AS M

Upvotes: 1

Mike Ryan
Mike Ryan

Reputation: 4374

A general SQL statement that would run this would be:

select P.PartNum, M.Formula, M.RevisionNum
from Parts P
join Material M on P.PartNum = M.PartNum
where M.RevisionNum = (select max(M2.RevisionNum) from Material M2
                       where M2.PartNum = P.PartNum);

Repeating the above caveats about what happens after Revision #26. The max(RevisionNum) may break depending upon what happens after #26.

EDIT:

If RevisionNum sequence always starts w/ NEW and then continues, A, B, C, etc., then the max() needs to be replaced w/ something more complicated (and messy):

select P.PartNum, M.RevisionNum
from Parts P
join Material M on P.PartNum = M.PartNum
where (
      (select count(*) from Material M2 
              where M2.PartNum = P.PartNum) > 1
      and M.RevisionNum = (select max(M3.RevisionNum) from Material M3
                   where M3.PartNum = P.PartNum and M3.RevisionNum <> 'NEW')
      )
      or ( 
      (select count(*) from Material M4
              where M4.PartNum = P.PartNum) = 1
       and M.RevisionNum = 'NEW'
      )

There must be a better way to do this. This works though -- will have to think about a faster solution.

SQL Fiddle: http://sqlfiddle.com/#!3/70c19/3

Upvotes: 4

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

If SQL Server 2005+

;WITH m AS 
(
   SELECT Partnum, Formula, RevisionNum,
     rn = ROW_NUMBER() OVER (PARTITION BY PartNum ORDER BY 
       CASE WHEN RevisionNum ='New' THEN 1 ELSE 2 END)
     FROM dbo.Material
)
SELECT p.PartNum, m.Formula, m.RevisionNum
FROM dbo.Parts AS p
INNER JOIN m ON p.PartNum = m.PartNum
WHERE m.rn = 1;

Though curious, what do you do when there are more than 26 revisions (e.g. what comes after Z)?

Upvotes: 5

Related Questions