Applying MAX on COUNT with sub queries and JOIN | SQL Oracle

Question

I'm trying to list the projects with the highest number of employees assigned to work on it.

This lists only the top number of employees on a given project...

SELECT MAX(number_employees)
FROM
    (SELECT COUNT(a.employee_id) AS number_employees
    FROM assignment a
    JOIN project p
    ON p.project_id = a.project_id
    GROUP BY a.project_id, p.project_name);

And this list all projects with the number of employees...

    
SELECT a.project_id, p.project_name, COUNT(a.employee_id) AS number_employees
    FROM assignment a
    JOIN project p
    ON p.project_id = a.project_id
    GROUP BY a.project_id, p.project_name;

However, I'm trying to list all projects but only the projects with the highest number of employees on them.

Thanks

Answer 1

You can use your first query with FETCH clause as follows:

SELECT a.project_id, p.project_name, COUNT(a.employee_id) AS number_employees
 FROM assignment a
 JOIN project p ON p.project_id = a.project_id
GROUP BY a.project_id, p.project_name
order by number_employees desc
fetch first 1 row WITH TIES