CODEWITHSUNDEEP
sqldatabasepostgresql
wasido
wasido

Reputation: 74

Find rows with same values PostgreSQL

In a table, there are ids with multiple values

id|value
--|------
1 |a
1 |b
2 |a
2 |b
3 |a
3 |c
4 |a
5 |a
5 |b
...

so how can i select all id's that have the same values as 1? What I want should look like this

id
--
1
2
5

Upvotes: 1

Views: 2533

Answers (2)

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4582

Also using arrays:

SELECT id
FROM (SELECT DISTINCT id FROM t ORDER BY 1) ids
WHERE ARRAY(SELECT value FROM t WHERE id = ids.id ORDER BY 1) =
      ARRAY(SELECT value FROM t WHERE id = 1 ORDER BY 1);

This should work even for duplicated and/or NULL values.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271141

If you want exactly the same values, you can use a set-based approach:

select t.id
from t join
     t t1
     on t.value = t1.value and t1.id = 1
group by t.id
having count(*) = (select count(*) from t where t.id = 1);

Assuming no duplicates, this counts the number of values that matches each id and then checks that there are the same number.

I should admit that the the string_agg() approach is also elegant:

select id
from (select id, string_agg(value, ',' order by value) as values,
             max(case when id = 1 then string_agg(value, ',' order by value)) over () as values_1
      from t
      group by id
     ) t
where values = values_1;

Upvotes: 5

Related Questions