Finding rows with same values in two columns

Question

I have 3 columns in Postgres database having table mytable and i want records having only duplicate values in 2nd and 3rd column.

SQL> select * from mytable ;

column1      column2       column3

  A            1            50----required output ie. 50 is duplicate in line B column 2
  B            50           3 ----required output ie. 50 is duplicate in line A column 3
  C            2            10----values are duplicate in others lines
  D            30           70----required output ie. 30 is duplicate in line E column 3
  E            8            30----required output ie. 30 is duplicate in line D column 2
  F            40           25----values are not duplicate in others lines

I want the following output with count(*):

 column1      column2       column3

  A            1            50
  B            50           3
  D            30           70
  E            8            30

user12571241 · Accepted Answer · 2021-04-05 21:18:37Z

1

Here is an example of a self join to handle this:

select distinct m.*
from mytable m
inner join mytable m2
    on (
        m.column2 in (m2.column2, m2.column3)
        or m.column3 in (m2.column2, m2.column3)
       )
    and m.column1 <> m2.column1

answered Apr 5, 2021 at 21:18

user12571241

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2021-04-05 21:35:45Z

0

I would use exists:

select t.*
from mytable t
where exists (select 1
              from mytable t2
              where t.col2 in (t2.col2, t2.col3) or
                    t.col3 in (t2.col2, t2.col3)
             );

answered Apr 5, 2021 at 21:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Finding rows with same values in two columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related