J-G1234 J-G1234 - 3 months ago 6
SQL Question

Select rows where column 1 value is the same but column 2 value is different in PostgreSQL

I'm working on PostgreSQL on a table with this structure :

________________
|project|person|
|_______|______|
|1 |P1 |
|1 |P2 |
|2 |P3 |
|2 |P3 |
|3 |P4 |
|_______|______|


I would like to write a SQL query to select ONLY the rows where one project has more than one person. In other words, I would like to select all the rows where the project is the same but the person is different, excluding the other rows. In my example it would return only the two first rows :

________________
|project|person|
|_______|______|
|1 |P1 |
|1 |P2 |
|_______|______|


I can not wrap my head arround this. Is saw this question which is basically the opposite but I don't see how to make it work for me.

I tried to fiddle with COUNT or HAVING for a long time but I can not grasp the logic of how I should build this query. For example I tried :

SELECT t.person, t.project
FROM table t
GROUP BY r.ide_proj, r.ide_pers
HAVING COUNT(t.person) > 1


but this gives me strange results, including rows whre one projects only has one person, or vice-versa.

Can somebody help me with this little problem ?

Many thanks !

Answer

Here's one option using a subquery to get the distinct rows and then a window function to get both the project and the person.

select * 
from (
    select *, count(person) over (partition by project) cnt
    from (select distinct project, person from yourtable) t 
    ) t
where cnt > 1

Or you could use a regular group by query, but you'll need to join the result back to the original table to get the names associated with each project.

select t.*
from yourtable t join (
    select project 
    from yourtable
    group by project 
    having count(distinct person) > 1) t2 on t.project = t2.project