Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.9k views
in Technique[技术] by (71.8m points)

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

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...