>>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
I was never sure it would give me the exactly the record in which it found the max() value. Probably did this at random back in 2.x, and I never learned to trust it. It's some Field4 that I'm interested in, and I'm still not sure I'd get it this way.