>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.


select max(field3) as MaxField3, field2, field3 from yourtable group by 2,3,1