Limited T-SQL support

Question

It should be simple enough, but somehow my brain stops working.

I have two related tables:

Table 1:

ID (PK), Value1 

Table 2:

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

Sample 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 entry in table 1, I would like to make the corresponding entry in table 2, but only the most recent (the batch identifier is sequential). The 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 the connection result using Table2. There were similar questions about SO, but they cannot find anything like mine. Here is one of MySQL that looks similar: SQL JOIN LIMIT

I am open to any approach, although speed is still a top priority as it will be a large data set.

Answer 1

 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 2

 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 3

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 

Answer 4

A GROUP BY or WHERE clause that filters 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 )