Selecting single row if it matches another

Question

I have a scenario of this type:

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

Answer 1

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