>>I need to find the employee id for the employee that has the most hours for each given work type.
>>
>>In VFP 6 the following SQL Select would work fine:
>>
SELECT worktype, MAX(asgnhrs), emplid FROM john GROUP BY worktype
>>
>>In VFP 9 I am trying to do things the new way without useing the set enginebehavior 70 command. Of course this generates the "Invaid Group By" error becuase I have not included the emplid in the group by. But, if I did, I would get all the emplyees. What do I need to change to get only the emplyee id with the max hours?
>>TIA.
>
>You must include all the fields in the group by list
>>
SELECT worktype, MAX(asgnhrs), emplid FROM john GROUP BY 1,2,3worktype
>or
>>
SELECT worktype, MAX(asgnhrs) as maxasg, emplid FROM john GROUP BY worktype,maxasg,emplid
Tori,
Thanks for the reply but your solution gives me the max asgnhrs for each and every employee. I need only the employee with the max hours for each worktype.
Beer is proof that God loves man, and wants him to be happy. - Benjamin Franklin
John J. Henn