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

Posted by z531 on Stack Overflow See other posts from Stack Overflow or by z531
Published on 2010-03-12T21:46:49Z Indexed on 2010/03/12 21:57 UTC
Read the original article Hit count: 412

Filed under:
|
|
|

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

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about tsql