SQL Server: SELECT rows with MAX(Column A), MAX(Column B), DISTINCT by related columns
- by z531
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
