CODEWITHSUNDEEP
sqlpostgresql
gene b.
gene b.

Reputation: 11994

SQL/Postgres: Find Tuple with Same Value (for a Given Value in another column)

My table ANSWERS_T is 

id    answer_date_val             event_id
..    ...                         ...
103   2019-10-18 09:30:00.000     145
104   2019-10-18 09:35:00.000     145
105   2019-10-18 10:45:00.000     146
106   2019-10-18 10:50:00.000     146
..

My query needs to find ANSWERS_T rows where there are multiple identical answer_date_val for the same event_id.

This is in Postgres.

Upvotes: 1

Views: 1036

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

If you need duplicated entry for answer_date_val and event_id, you could try using a subquery for duplicated joined to the base table

  select * 
  from ANSWERS_T a 
  inner join  (
      select  event_id, answer_date_val
      from  ANSWERS_T
      group  by event_id, answer_date_val
      having count(*) >= 2 
  ) t on t.event_id= a.event_id
      and t.answer_date_val = a.answer_date_val

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can get the rows using:

select a.*
from answers_t a
where exists (select 1
              from answers_t a2
              where a2.event_id = a.event_id and
                    a2.answer_date_val = a.answer_date_val and
                    a2.id <> a.id
             );

I would be inclined, though, to summarize this:

select event_id, answer_date_val, array_agg(id)
from answers_t
group by event_id, answer_date_val
having count(*) > 1;

Upvotes: 1

Related Questions