Select only those records which are twice in postgres

Question

select distinct(msg_id),sub_id from programs where sub_id IN
(
select sub_id from programs group by sub_id having count(sub_id) = 2 limit 5
)

sub_id means subscriberID

Inner query will return those subscriberID which are exactly 2 times in the program table and main query will gives those subscriberID which having distinct msg_id.

This result will generated

msg_id  sub_id
------|--------|
 112  |  313
 111  |  222
 113  |  313
 115  |  112
 116  |  112
 117  |  101
 118  |  115
 119  |  115
 110  |  222

I want it should be

    msg_id  sub_id
    ------|--------|
     112  |  313
     111  |  222
     113  |  313
     115  |  112
     116  |  112
     118  |  115
     119  |  115
     110  |  222

117 | 101 (this result should not be in output because its only once)

I want only those record which are twice.

Answer 1

I'm not sure, but are you just missing the second field in your in-list?

select distinct msg_id, sub_id, <presumably other fields>
from programs
where (sub_id, msg_id) IN
(
  select sub_id, msg_id
  from programs
  group by sub_id, msg_id
  having count(sub_id) = 2
)

If so, you can also do this with a windowing function:

with cte as (
  select
    msg_id, sub_id, <presumably other fields>,
    count (*) over (partition by msg_id, sub_id) as cnt
  from programs
)
select distinct
  msg_id, sub_id, <presumably other fields>
from cte
where cnt = 2

Answer 2

try this

SELECT msg_id, MAX(sub_id)
FROM programs 
GROUP BY msg_id
HAVING COUNT(sub_id) = 2  -- COUNT(sub_id) > 1 if you want all those that repeat more than once
ORDER BY msg_id