join tables with max record

[nhnemo]

I want to create a view and join two tables together on an identity field it’s a one to many relationship. Basically I have to do a right outer joint but the problem is I only want the record with the latest date in the second table.

For example
If this is the left table:
Identity info_1
1 yyy
2 zzz

Right table:
Identity date info_2
1 3/11/2001 aaa
1 3/12/2001 bbb
1 3/13/2001 ccc
2 3/10/2001 ddd

I need the results to be like this
Identity info_1 info_2 date
1 yyy ccc 3/13/2001
2 zzz ddd 3/10/2001

Thank you,

[deric]

Can you post what you have so far?

[vksingh24]

Can you post what you have so far?

what is the criteria of latest date? Is it the today date?

[ramdil]

Hi

SELECT [Identity date info_2].Id, Max([Identity date info_2].Date) AS MaxOfDate, [Identity info_1].Nam,max([Identity date info_2].Nam)
FROM [Identity date info_2] INNER JOIN [Identity info_1] ON [Identity date info_2].Id = [Identity info_1].Id
GROUP BY [Identity date info_2].Id, [Identity info_1].Nam

Hope above query helps you