• Here's how I would do it with the datestamp field:

    CREATE Table #Table_A (A_ID INT,F_Name VARCHAR(2), M_Name VARCHAR(2),L_Name1 VARCHAR(2))

    INSERT INTO #Table_A

    SELECT 1,'A','B','C'UNION ALL

    SELECT 2,'X','Y','Z'

    CREATE Table #Table_B(B_ID INT,A_ID INT,City VARCHAR(5),State VARCHAR(2),Zip VARCHAR(10),Entered DATETIME)

    INSERT INTO #Table_B

    SELECT 1,1,'xxx','TN','37013',GETDATE() UNION ALL

    SELECT 2,1,'yyy','TX','37012',GETDATE() - 40 UNION ALL

    SELECT 2,2,'aaa','UT','44030', GETDATE() - 5

    SELECT *

    FROM #Table_A a

    LEFT OUTER JOIN

    (SELECT B_ID,b.A_ID,City,State,Zip

    FROM #Table_B b

    INNER JOIN

    (SELECT A_ID,bid=min (Entered)

    FROM #Table_B

    GROUP BY A_ID) as derived2

    ON derived2.bid=b.Entered AND derived2.A_ID =b.A_ID) as derived1

    ON a.A_ID=derived1.A_ID

    DROP TABLE #Table_A

    DROP TABLE #Table_B

    Basically the same thing. Note that adding the AND derived2.A_ID =b.A_ID to my original will also fix the issue of duplicate B_IDs throwing it off.

    This does fail on the case where 2 records of B with the same A_ID have identical timestamps. Id need more nesting to get around that (min datetime then min ID), so I think your row_number solution probably makes more sense for the date issue.