>>Thanks Tom that worked great.
>
>Are you sure it gave what you wanted?
>
>Because VFP doesn't enforce all ANSI-92 rules, you can get into trouble using GROUP BY.
>
>You should find that the data actually being retrieved by the SQL statement is for records 2 and 3 not 1 and 3. This happens when extra fields are returned that are not part of an aggregate and are not included in the GROUP BY clause. VFP takes the values for the extra fields from the record located physically last in the table corresponding to the group.
>
>I don't believe there is an easy way to do this using simple SQL. You have to process the records involved so VFP code is probably your best bet.
Right you are. I had done something recently that was very similar to Michael's request. My results were accurate in my case because the last ones for each grouping were the ones I needed to return.
It would take two statements to return the first values of the non-grouped fields.
One way might be:
SELECT FLD1,FLD2,FLD3,FLD4 FROM mytable;
ORDER BY fld4 desc,fld3 desc INTO CURSOR tmp
SELECT FLD1, FLD2, FLD3, FLD4 FROM tmp INTO CURSOR mycursor ;
GROUP BY fld1,fld2
Tom