How can I select all rows from different tables when the query returns multiple min(count) data

Question

Consider I have the below tables created and the relationships are created as per the ER Diagram.

I have been asked to find which project(s) have the least number of employees. I tried to use the below code, but I am getting only 1 output, whereas I am expecting two records to be created as per the data fed:

select pname from project p where pnumber = (select min(count) from (select count(*) from works_on group by pno)t)

Below are the data in the table:

Employee table:

Project Table:

Works_On Table:

score 2 · Answer 1 · answered Sep 21 '20 at 22:04

In Postgres 13 (currently RC), this becomes dead simple using the new WITH TIES:

SELECT pno
FROM   works_on
GROUP  BY 1
ORDER  BY count(*)
FETCH  FIRST 1 ROWS WITH TIES;  -- new & hot

If you need more project attributes, join to table project after identifying the "winning" project numbers cheaply form works_on alone. That's fastest.

SELECT p.*
FROM  (
   SELECT pno AS pnumber
   FROM   works_on
   GROUP  BY 1
   ORDER  BY count(*)
   FETCH  FIRST 1 ROWS WITH TIES
   ) w
JOIN  project p USING (pnumber);

Get apps with the highest review count since a dynamic series of days

score 0 · Answer 2 · answered Sep 21 '20 at 21:07

I would recommend aggregation and window functions.

The logic is to count the number of employees per project, then use window functions to rank the projects. ran() comes handy, because it allows ties:

select *
from (
    select p.*, count(*) cnt_employees, rank() over(order by count(*)) rn
    from project p
    inner join works_on wo on wo.pno = p.pnumber
    group by p.pno
) t
where rn = 1

How can I select all rows from different tables when the query returns multiple min(count) data

2 Answers2