Why this first query doesn't work but the second query works?-CodePudding

Project_id	Employee_id
1	1
1	2
2	1

Write a sql query that reports all the projects that have the most employee

Project_id
1

select temp.project_id from
(select project_id, count(distinct employee_id)
  from Project
  group by project_id
  order by count(distinct employee_id) desc
  )temp
limit 1

select project_id from Project
group by project_id
having count(project_id)=
  (select count(project_id) from Project
   group by Project_id
   order by count(project_id) desc
   limit 1)

CodePudding user response：

Assuming employee_ids are unique per project, both would work. Otherwise first would work.

DBFiddle demo

EDIT: This DBFiddle demo explains why first one would always work while second wouldn't if employee_ids are not unique per project.