>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, except the aggregate fields, in the group by list
>
SELECT worktype, MAX(asgnhrs), emplid FROM john GROUP BY 1,3
or
>
SELECT worktype, MAX(asgnhrs), emplid FROM john GROUP BY worktype,emplid