You can use derived table.
select dt1.pkfield, dt1.maxfield, table1.anotherfield
from (SELECT pkfield, max(field2) as maxfield from table1
group by pkfield ) dt1
inner join table1 on table1.pkfield = dt1.pkfield
>Amazing!
>
>I would not have guessed that you can perform a select to produce a column! If we can't do that in VFP, they should add that in!
>
>BUT! I still think I need 2 selects. No one has yet answered how I can do two consecutive selects! I have 2 tables. The second table is a log of events. I want to grab fields besides the Max() one from the record with the max date.
>
>>
select pkfield, table1.anotherfield,
>> ( SELECT max(field2) from table1 t12 where t11.pkfield = t12.pkfield) as maxfield
>>from table1 t11
>>
>>>Hi Fred
>>>
>>>I really must have two consecutive SQLs as I need MaxField in the final output. Your answer was right, since I asked the wrong question! The second SQL should have been ...
>>>
>><snip>
--sb--