>I have following table
>
>REP COUNTRY SALES
>01 IRL 600
>01 UK 500
>01 USA 400
>02 FRA 300
>02 GER 200
>02 ITA 100
>
>Set Enginebehavior 70
>Select Rep,Country, *,Max(Sales) as MySales from MyTable Group by REP ..results in
>01 USA 400
>02 ITA 100
>
>It picks the Sales ok but country arbitrarily (I think)
>
>The country I want is the country on the row with the Highest Sale i.e.
>01 IRL 600
>02 FRA 300
>
>Is there any way in SQL to do this ??
>
>Tia
>Gerard

That is why Set Enginebehavior 90 was introduced.

Set Enginebehavior 90
SELECT MyTable.*;
FROM MyTable;
INNER JOIN (SELECT Rep,;
                   MAX(SALES) AS SALES;
              FROM MyTable;
            GROUP BY Rep) MTbl1 ON MyTable.Rep = MTbl1.Rep AND MyTable.Sales = MTbl1.Sales;
INTO CURSOR crsTest
BROW NORMAL