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: 549
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