CODEWITHSUNDEEP
sqloracle-databasecountsubquerymax
Rubikted
Rubikted

Reputation: 41

Use multiple tables in sub queries

I need to display the number of tasks an employee has, but only the employees who have the highest number of tasks. The problem I have is I can't figure out how to display if only the employees have the highest number of tasks.

The number of tasks are in the task, and the employees are linked to the task by employee_id.

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, 
COUNT(E.TASK_ID)NUMBER_OF_TASKS
 FROM EMPLOYEE E, TASK T
WHERE E.EMPLOYEE_ID = T.EMPLOYEE_ID
GROUP BY E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME;

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use window functions. I would write this as:

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, T.NUMBER_OF_TASKS
FROM EMPLOYEE E JOIN
     (SELECT T.EMPLOYEE_ID, COUNT(*) as NUMBER_OF_TASKS,
             MAX(COUNT(*)) OVER () as MAX_NUMBER_OF_TASKS
      FROM TASK T
      GROUP BY T.EMPLOYEE_ID
     ) T
     ON E.EMPLOYEE_ID = T.EMPLOYEE_ID
WHERE t.NUMBER_OF_TASKS = t.MAX_NUMBER_OF_TASKS;

Oh, also note the use of proper, explicit, standard JOIN syntax.

Upvotes: 1

Related Questions