>
>Hi,
>>
>>Table A is parent-> child with table B
>>A has PKey and MyName fields
>>Table B has a PKey, FKey (to A) and MyDate fields
>>
>>in VFP I can doselect A.pkey, B.Pkey, max(MyDate) from a join b on b.fkey = a.pkey group by a.pkey
>>and get the record in B with the latest date.>
>You're mistaken about that. The B.Pkey value comes from the last record in the group not from the record with the MAX MyDate. You'll get correct result only if the records in the b.fkey group are ordered physicaly by MyDate in the B table.
>
>
>in SQL Server, however, I must group on both A.PKey, and B.Pkey - so question - how can I get from SQL Server a result set like
>>A.Pkey B.Pkey where the second field is the key of the record related to A with the latest date?
>>Try
select A.pkey, B.Pkey, b.MyDate
> from a join b on b.fkey = a.pkey
> WHERE b.mydate = ( SELECT max(MyDate) FROM b bb
> WHERE bb.fkey = a.pkey)
>
Thanks - that gives me some ideas - what I failed to say was that there is actually a third table (another parent of B) that has the actual date in it - got to figure out all the joins here
Ken B. Matson
GCom2 Solutions