CODEWITHSUNDEEP
sqlpostgresql
tiredqa_18
tiredqa_18

Reputation: 331

How to get only rows with duplicate id with specific values of a column

I have a table with these two columns

+------+------+
|id    |type  |
+------+------+
|1     |A     |
+------+------+
|1     |B     |
+------+------+
|2     |B     |
+------+------+
|3     |A     |
+------+------+
|3     |B     |
+------+------+
|3     |C     |
+------+------+
|4     |A     |
+------+------+
|4     |A     |
+------+------+

and I want to get rows only with duplicate id that has value A and B for Type column

It should look something like this:

+------+------+
|id    |type  |
+------+------+
|1     |A     |
+------+------+
|1     |B     |
+------+------+
|3     |A     |
+------+------+
|3     |B     |
+------+------+

I tried the query below which does print out rows with only the duplicate ids but wasn't able to get rows with value A and B only

select id, type from table s1
where (select count(id) from table s2 where s2.id = s1.id and type in ('A', 'B')) > 1 
group by id, type
order by id

Upvotes: 0

Views: 1128

Answers (1)

Popeye
Popeye

Reputation: 35900

You can use the count analytical function as follows:

select id, type from
(select id, type, count(distinct type) over (partition by id) as cnt
  from t where type in ('A','B')) t
where cnt = 2

You can also use EXISTS as follows:

select id, type from your_table  t 
 where type in ('A','B')
   and exists (select 1 from your_table tt
                where t.id = tt.id 
                  and tt.type in ('A','B')
                  and tt.type <> t.type)

Upvotes: 1

Related Questions