SQL Server: SELECT rows with MAX(Column A), MAX(Column B), DISTINCT by related columns

Question

Scenario:

Table A
MasterID, Added Date, Added By, Updated Date, Updated By,
1, 1/1/2010, 'Fred', null, null
2, 1/2/2010, 'Barney', 'Mr. Slate', 1/7/2010
3, 1/3/2010, 'Noname', null, null

Table B
MasterID, Added Date, Added By, Updated Date, Updated By,
1, 1/3/2010, 'Wilma', 'The Great Kazoo', 1/5/2010
2, 1/4/2010, 'Betty', 'Dino', 1/4/2010

Table C
MasterID, Added Date, Added By, Updated Date, Updated By,
1, 1/5/2010, 'Pebbles', null, null
2, 1/6/2010, 'BamBam', null, null

Table D
MasterID, Added Date, Added By, Updated Date, Updated By,
1, 1/2/2010, 'Noname', null, null
3, 1/4/2010, 'Wilma', null, null

I need to return the max added date and corresponding user, and max updated date and corresponding user for each distinct record when tables A,B,C&D are UNION'ed, i.e.:
1, 1/5/2010, 'Pebbles', 'The Great Kazoo', 1/5/2010
2, 1/6/2010, 'BamBam', 'Mr. Slate', 1/7/2010
3, 1/4/2010, 'Wilma', null, null

I know how to do this with one date/user per row, but with two is beyond me.
DBMS is SQL Server 2005. T-SQL solution preferred.

Thanks in advance,
Dave

Answer 1

Do as you would with one date/user per row, and repeat for modified date, then join the two resulting tables together on the MasterID.

SELECT added.MasterID, added.AddedDate, added.AddedBy, modif.UpdatedDate, modif.UpdatedBy FROM
(
SELECT a.MasterID, a.AddedDate, b.AddedBy FROM
(
    SELECT MasterID, Max(AddedDate) As AddedDate FROM
    (
        SELECT * FROM TableA
        UNION ALL
        SELECT * FROM TableB
        UNION ALL
        SELECT * FROM TableC
        UNION ALL
        SELECT * FROM TableD
    )
    GROUP BY MasterID
) a
JOIN
(
        SELECT * FROM TableA
        UNION ALL
        SELECT * FROM TableB
        UNION ALL
        SELECT * FROM TableC
        UNION ALL
        SELECT * FROM TableD
) b
ON a.MasterID = b.MasterID AND a.AddedDate = b.AddedDate
) added
LEFT OUTER JOIN
(
SELECT a.MasterID, a.UpdatedDate, b.UpdatedBy FROM
(
    SELECT MasterID, Max(UpdatedDate) As UpdatedDate FROM
    (
        SELECT * FROM TableA
        UNION ALL
        SELECT * FROM TableB
        UNION ALL
        SELECT * FROM TableC
        UNION ALL
        SELECT * FROM TableD
    )
    GROUP BY MasterID
) a
JOIN
(
        SELECT * FROM TableA
        UNION ALL
        SELECT * FROM TableB
        UNION ALL
        SELECT * FROM TableC
        UNION ALL
        SELECT * FROM TableD
) b
ON a.MasterID = b.MasterID AND a.UpdatedDate = b.UpdatedDate
) modif
ON added.MasterID = modif.MasterID