How do I output the "most" in MySql?

Question

I have a list of employees for our application. One of the fields is job title. I use the 4000 job titles to roll people into 1 of 50 "job roles". We then give people access to our apps via job roles.

On this list we have their supervisor email associated too. Their supervisor can assign them something on our apps but needs a job role. Note that supervisors have a "generic manager" job title which rolls into a generic manager "job role".

So based on

employeeid supervisorid jobrole

How can I parse through my list get all of the employees under a supervisor and then assign the supervisor a "suprole" base on what jobrole most of their employees have?

Example is:

Ted, Dan, Fred, Susan, and Bill report to Robert.

Their job roles are: Ted - dishwasher Dan - cook Fred - server Susan - cook Bill - cook

I would expect Robert's "suprole" to be cook.

Answer 1

update employee as a 
      set a.suprole=(
                    select b.jobroll from employee as b 
                           where b.supervisorid=a.employeeid 
                           group by b.jobroll 
                           order by count(b.jobroll) desc 
                           limit 1
                     ) 
       where a.jobroll='generic manager';