CODEWITHSUNDEEP
sql
Dmitriy Brussintsov
Dmitriy Brussintsov

Reputation: 45

SQL:how to find duplicated combinations in two adjacent columns

I have a table (field1, field 2, field3, field4), How can I sift out only those rows which contain duplicated combinations in two adjacent columns - field3 and field4? i.e -> enter image description here

Upvotes: 2

Views: 1640

Answers (1)

Bohemian
Bohemian

Reputation: 425258

Try this:

select *
from mytable t
join (
    select field3, field4, count(*) from (
      select field3, field4 from mytable where field3 <= field4
      union all
      select field4, field3 from mytable where field3 > field4) x
    group by field3, field4
    having count(*) > 1) y
  on (t.field3 = y.field3 and t.field4 = y.field4)
    or (t.field3 = y.field4 and t.field4 = y.field3)

The union all inner query lines up all the values, without removing duplicates (as union does) into consistent columns - the where clauses ensure that rows aren't selected twice.

The inner query is then grouped with a having clause to pick the duplicates.

The outer query joins to these both ways to get all the rows.

Upvotes: 1

Related Questions