CODEWITHSUNDEEP
sqlpostgresql
Pracede
Pracede

Reputation: 4361

Finding rows with same values in two columns

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

Upvotes: 0

Views: 122

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269447

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)
             );

Upvotes: 0

Austin
Austin

Reputation: 2265

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

Upvotes: 1

Related Questions