Try
SELECT t1.* FROM mytable t1
WHERE EXISTS ( SELECT * FROM mytable t2
WHERE t2.keyfield = t1.keyfield
AND t1.field1 = MAX( t2.field1))
>Hi everyone:
>
>Would someone please point me to a thread(s) wheer I can see a discussion of the syntax issues of grouping and returning (say) the max() of a field in the group, together with the correct row that corresponds to the max?
>
>
>E.g.
>Select max(myTable.row1), max(myTable.row2), max(myTable.row3), ...
>From myTable
>Group by myTable.row1
>
>
>Running something like this gives values for the third row that are erratic.
>
>I have a feeling that a subquery is required.
>
>I have re-subscribed to PUTM but the sub has not yet taken effect and the search values are all hashed out.
>
>TIA
>
>Godfrey
--sb--