Limited T-SQL Join

Question

This should be simple enough, but somehow my brain stopped working.

I have two related tables:

Table 1:

ID (PK), Value1

Table 2:

BatchID, Table1ID (FK to Table 1 ID), Value2

Example data:

Table 1:

ID  Value1
1   A
2   B

Table 2:

BatchID  Table1ID  Value2
1        1         100
2        1         101
3        1         102
1        2         200
2        2         201

Now, for each record in Table 1, I'd like to do a matching record on Table 2, but only the most recent one (batch ID is sequential). Result for the above example would be:

Table1.ID  Table1.Value1  Table2.Value2
1          A              102
2          B              201

The problem is simple, how to limit join result with Table2. There were similar questions on SO, but can't find anything like mine. Here's one on MySQL that looks similar: http://stackoverflow.com/questions/494974/limiting-an-sql-join

I'm open to any approach, although speed is still the main priority since it will be a big dataset.

Answer 1

Either GROUP BY or WHERE clause that filters on the most recent:

SELECT * FROM Table1 a
INNER JOIN Table2 b ON (a.id = b.Table1ID)
WHERE NOT EXISTS(
      SELECT 1 FROM Table2 c WHERE c.Table1ID = a.id AND c.BatchID > b. BatchID
)

Answer 2

WITH Latest AS (
    SELECT Table1ID
        ,MAX(BatchID) AS BatchID
    FROM Table2
    GROUP BY Table1ID
)
SELECT *
FROM Table1
INNER JOIN Latest
    ON Latest.Table1ID = Table1.ID
INNER JOIN Table2
    ON Table2.BatchID = Latest.BatchID

Answer 3

SELECT  id, value1, value2
FROM    (
        SELECT  t1.id, t2.value1, t2.value2, ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t2.BatchID DESC) AS rn
        FROM    table1 t1
        JOIN    table2 t2
        ON      t2.table1id = t1.id
        ) q
WHERE   rn = 1

Answer 4

Try

select t1.*,t2.Value2
from(
select Table1ID,max(Value2) as Value2
from [Table 2]
group by Table1ID) t2
join [Table 1] t1 on t2.Table1ID = t1.id