I must be sleepy :-)I thougth you want only one policy with the MAX(salary). Sorry.
>>>I have a table with the following fields sif_no, ClassCode, Salary. There are many ClassCodes for a policy. I want to get the ClassCode with the highest salary. See snippet of code below that I use. However, if there is a policy(sif_no) with two class codes with the same salary it will pick up both.
>>>
>>>I would like to have only 1 ClassCode returned in the query and it doesn't matter which ClassCode is selected.
>>>
>>>
>>>
>>>***get the max salary and class code
>>>Select sif_no, class_code, salary ;
>>> from tmpmem_class a ;
>>> WHERE salary = ( Select Max(salary) From tmpmem_class b ;
>>> WHERE a.sif_no = b.sif_no) ;
>>> INTO Cursor tmpmax
>>>
>>>
>>>
>>>
>>>
>>>Any suggestions.
>>>
>>>Gaylen
>>
>>
>>***get the max salary and class code
>>Select TOP 1 sif_no, class_code, salary ;
>> from tmpmem_class a ;
>> WHERE salary = ( Select Max(salary) From tmpmem_class b ;
>> WHERE a.sif_no = b.sif_no) ;
>> ORDER BY class_code;
>> INTO Cursor tmpmax
>>
>>
>Borislav
>
>Unfortunately that only returns 1 record and not a record for each policy.
>
>Sergey used a different approach in his reply and that seems to work.
>
>Thanks
>Gaylen
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.