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.