how to choose distinct values from the pairs of names in sql?

Question

The table f has these values:

name1           name2           count
Harrison Ford   Mullah Omar     3
Harrison Ford   Michael Caine   6
Michael Caine   Mullah Omar     2
Michael Caine   Harrison Ford   6
Mullah Omar     Michael Caine   2
Mullah Omar     Harrison Ford   3

How to choose only the distinct pairs?

select 
distinct name1, 
distinct name2, count from f group by name1

Answer 1

Given that you have a duplicate for each pair, you can just use:

select f.*
from f
where name1 < name2;

If you did not have the reverse in all cases, you can use:

select f.*
from f
where f.name1 < f.name2 or
      not exists (select 1 
                  from f f2
                  where f2.name1 = f.name2 and f2.name2 = f.name1
                 );