By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

join tables with max record

P: 1
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,
Mar 24 '08 #1
Share this Question
Share on Google+
3 Replies

P: 92
Can you post what you have so far?
Mar 25 '08 #2

P: 21
Can you post what you have so far?

what is the criteria of latest date? Is it the today date?
Mar 25 '08 #3

P: 46

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
Mar 25 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.