Limited T-SQL Join

join, limit, sql-server, t-sql

Solution

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

Problem

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: LIMITing an SQL JOIN I'm open to any approach, although speed is still the main priority since it will be a big dataset.

Original source