Join to only the “latest” record with t-sql

问题:

I've got two tables. Table "B" has a one to many relationship with Table "A", which means that there will be many records in table "B" for one record in table "A".

The records in table "B" are mainly differentiated by a date, I need to produce a resultset that includes the record in table "A" joined with only the latest record in table "B". For illustration purpose, here's a sample schema:

Table A
-------
ID

Table B
-------
ID
TableAID
RowDate

I'm having trouble formulating the query to give me the resultset I'm looking for any help would be greatly appreciated.

回答1:

select a.*, bm.MaxRowDate
from (
    select TableAID, max(RowDate) as MaxRowDate
    from TableB
    group by TableAID
) bm
inner join TableA a on bm.TableAID = a.ID

If you need more columns from TableB, do this:

select a.*, b.* --use explicit columns rather than * here
from (
    select TableAID, max(RowDate) as MaxRowDate
    from TableB
    group by TableAID
) bm
inner join TableB b on bm.TableA = b.TableA
    and bm.MaxRowDate = b.MaxRowDate
inner join TableA a on bm.TableAID = a.ID

回答2:

SELECT *
FROM tableA A
OUTER APPLY (SELECT TOP 1 * 
             FROM tableB B
             WHERE A.ID = B.TableAID
             ORDER BY B.RowDate DESC) as B

回答3:

With ABDateMap AS (
    SELECT Max(RowDate) AS LastDate, TableAID FROM TableB GROUP BY TableAID
),
LatestBRow As (
    SELECT MAX(ID) AS ID, TableAID FROM ABDateMap INNER JOIN TableB ON b.TableAID=a.ID AND b.RowDate = LastDate GROUP BY TableAID
)
SELECT columns
FROM TableA a
INNER JOIN LatestBRow m ON m.TableAID=a.ID
INNER JOIN TableB b on b.ID = m.ID

回答4:

table B join is optional: it depends if there are other columns you want

SELECT
    *
FROM
    tableA A
    JOIN
    tableB B ON A.ID = B.TableAID
    JOIN
    (
    SELECT Max(RowDate) AS MaxRowDate, TableAID
    FROM tableB
    GROUP BY TableAID
    ) foo ON B.TableAID = foo.TableAID AND B.RowDate= foo.MaxRowDate

回答5:

Just for the clarity's sake and to benefit those who will stumble upon this ancient question. The accepted answer would return duplicate rows if there are duplicate RowDate in Table B. A safer and more efficient way would be to utilize ROW_NUMBER():

Select a.*, b.* -- Use explicit column list rather than * here
From [Table A] a
Inner Join ( -- Use Left Join if the records missing from Table B are still required
    Select *,
        ROW_NUMBER() OVER (PARTITION BY TableAID ORDER BY RowDate DESC) As _RowNum
    From [Table B]
) b
On b.TableAID = a.ID
Where b._RowNum = 1

回答6:

Try using this:

    BEGIN

DECLARE @TB1 AS TABLE (ID INT, NAME VARCHAR(30) )
DECLARE @TB2 AS TABLE (ID INT, ID_TB1 INT, PRICE DECIMAL(18,2))

INSERT INTO @TB1 (ID, NAME) VALUES (1, 'PRODUCT X')
INSERT INTO @TB1 (ID, NAME) VALUES (2, 'PRODUCT Y')

INSERT INTO @TB2 (ID, ID_TB1, PRICE) VALUES (1, 1, 3.99)
INSERT INTO @TB2 (ID, ID_TB1, PRICE) VALUES (2, 1, 4.99)
INSERT INTO @TB2 (ID, ID_TB1, PRICE) VALUES (3, 1, 5.99)

INSERT INTO @TB2 (ID, ID_TB1, PRICE) VALUES (1, 2, 0.99)
INSERT INTO @TB2 (ID, ID_TB1, PRICE) VALUES (2, 2, 1.99)
INSERT INTO @TB2 (ID, ID_TB1, PRICE) VALUES (3, 2, 2.99)


SELECT A.ID, A.NAME, B.PRICE
  FROM @TB1 A
  INNER JOIN @TB2 B ON A.ID = B.ID_TB1 AND B.ID = (SELECT MAX(ID) FROM @TB2 WHERE ID_TB1 = A.ID)


END

回答7:

This will fetch the latest record with JOIN. I think this will help someone

SELECT cmp.*, lr_entry.lr_no FROM 
(SELECT * FROM lr_entry ORDER BY id DESC LIMIT 1) 
lr_entry JOIN companies as cmp ON cmp.id = lr_entry.company_id