Join to Select only a newest child record

[angjbsca]

Hi, I have this...

t1
ID,Name,Grp
1 pepe blue
2 juan black

t2
ID,T1FK,Date
1 1 01/01/2006
2 1 01/03/2006
3 1 01/02/2005

my join right now get:
1 , 1 , blue,01/01/2006
1 , 2 , blue,01/03/2006
1 , 3 , blue,01/02/2005

I want this:
1 , 2 , blue,01/03/2006

 

[gmmastros]

Select A.T1FK, 
       T2.Id, 
       A.TheDate,
       T1.Name,
       T1.Grp
From   T2
       Inner Join (
         Select T1FK, Max(TheDate) As TheDate
         From   T2
         Group By T1FK
         ) As A 
         On  T2.T1FK = A.T1FK
         And T2.TheDate = A.TheDate
       Inner join T1 On T2.T1FK = T1.Id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[angjbsca]

Thank You