How to Get Last Record In Table B for Table A

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to Get Last Record In Table B for Table A

Author

Topic

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2011-05-25 : 00:13:20

So here is my basic tables

Table A

RecordID TableAID Date Value
1 a1 1-1-11 abc
2 a2 2-2-11 def
3 a3 3-3-11 ghi

Table B

RecordID TableAID Date Value
1 a1 2-2-11 x1
2 a1 3-3-11 x2
3 a1 4-4-11 x3
4 a2 10-2-11 x4
5 a2 13-2-11 x5
6 a3 5-5-11 x6

I want to be able to run a query that returns the last value in Table B for the records in table A therefore have the following data

TableA.ID TableA.date, TableA.value TableB.date TableB.Summary

a1 1-1-11 abc 4-4-11 x3
a2 2-2-11 def 13-2-11 x5
a3 3-3-11 ghi 5-5-11 x6

Any advice?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-25 : 02:06:28

select
TableA.ID TableA.date, TableA.value TableB.date TableB.Value as Summary
from Table_A as A
join
(
select
row_number() over (partition by TableAID order by Date DESC) as rownum,
*
from Table_B
)B
on B.TableAID = A.TableAID and B.rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources