I have a scenario of this type:
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