Skip to content
Advertisement

Selecting single row if it matches another

I have a scenario of this type:

enter image description here

I want to select only one row in such a scenario. How can I achieve this?

Advertisement

Answer

You could use distinct on with least() and greatest():

select distinct on (least(name1, name2), greatest(name1, name2)) t.*
from mytable t
order by least(name1, name2), greatest(name1, name2), name1

An alternative is not exists:

select *
from mytable 
where not exists (
    select 1
    from mytable t1
    where t1.name1 = t.name2 and t1.name2 = t.name1 and t1.name1 < t1.name2
)

Finally: if all of the rows are duplicated, then a simple where condition is sufficient:

select *
from mytable
where name1 < name2
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement