CODEWITHSUNDEEP
sqlpostgresql
anderish
anderish

Reputation: 1759

PSQL Find Common Values In Different Groups

Lets say I have a bunch of team_id's. 

team_id|username
-------|--------
      1|      u1
      1|      u2
      2|      u3
      2|      u1
      2|      u2
      3|      u1
      3|      u2
      3|      u4

So for every team_id I want to find if any two users are always have the same team_id. So in the example above, the query should return u1 and u2 since they always in the same team. Kind of hard to explain...any ideas?

Upvotes: 0

Views: 206

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51496

build data:

t=# create table so17(team_id int,username text);
CREATE TABLE
t=# copy so17 from stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>       1|      u1
      1|      u2
      2|      u3
      2|      u1
      2|      u2
      3|      u1
      3|      u2
      3|      u4>> >> >> >> >> >> >>
>> \.
COPY 8

select:

t=# with e as (with i as (select *,array_agg(team_id) over (partition by username) from so17)
select distinct username,count(1) over (partition by array_agg) from i)
select username from e where count > 1;
 username
----------
       u2
       u1
(2 rows)

Upvotes: 1

Related Questions