You can do this in two steps
SELECT field1, field2, MAX(field3) AS max3 ;
FROM curFirst ;
group by Field1, Field2 ;
INTO CURSOR crsTemp
SELECT * ;
FROM curFirst ;
WHERE STR(Field1) + STR(Field2) + STR(Field3) ;
IN ( SELECT STR(Field1) + STR(Field2) + STR(max3)
FROM crsTemp );
INTO CURSOR crsResult
>Here's the scenario:
>
>
select * from curFirst group by Field1, Field2;
> having field3=max(field3) into cursor curSecond
>
>Field3 is an integer with values 1, 2 or 3. Grouping by Field1 and Field2 should make groups of zero to three records (i.e. combination of field1+field2+field3 is unique). I need to retrieve the one record from each group which has the highest value in Field3.
>
>The trouble with the above is that it retrieves only the records which have one record in each group. In my test case, I had six records in four groups; two groups had a values of 1 and 2 in field3, and the other two had value of 1. Only the latter two were returned.
>
>I tried using "where" instead of "having", but the SQL parser took the Max() function as max(e1, e2, e3...) and not as an aggregate function.
>
>Is there an easy way out of this? I don't need anything calculated here, I just want to pull out the records which have the greatest value of Field3 in their group.
--sb--