Write a query which shows which employee has had the most jobs with the company.
ID: 3675284 • Letter: W
Question
Write a query which shows which employee has had the most jobs with the company. There may be multiple employees with the biggest job count. Show the surname,forename, and the job count in your answer. You will have to GROUP BY two columns (e.g. GROUP BY x,y).Write a query which shows which employee has had the most jobs with the company. There may be multiple employees with the biggest job count. Show the surname,forename, and the job count in your answer. You will have to GROUP BY two columns (e.g. GROUP BY x,y).
Explanation / Answer
SELECT Empoyee.surname, Empoyee.forename FROM Employee
jobhistory.COUNT(*) as jobcount FROM jobhistory
GROUP BY jobhistory.EMPNO
GROUP BY surname,forename
HAVING jobhistory.jobcount = (select jobhistory.MAX(jobcount) ) FROM jobhistory )