>>This should get you started
>>
>>SELECT * FROM ChildTable ct WHERE NOT EXISTS (SELECT 1 FROM ChildTable WHERE pk = ct.pk and SomeDate > ct.SomeDate)
>>
>
>Thanks, but this returns all records from the child table.
Michel,
Why not take a moment to read the links I gave you to? They are really comprehensive (the first one especially) and show you and explain all various techniques of solving this problem.
And just in case another solution for you:
select P.*, C.*
from parent P
cross apply
(select top 1 * from Child where Child.ClientID = P.ClientID order by ModDate DESC) C
If it's not broken, fix it until it is.
My Blog