remove duplicated set of columns in table

Question

I created co-occurrence table as follows.

col1 col2 count
a    b    10
b    a    10
c    d    7
d    c    7

I want to keep co-occurrence rows without duplication like this.

col1 col2 count
a    b    10
c    d    7

How can I do this?

Answer 1

One simple method is:

select col1, col2, count
from t
where col1 < col2;

If you actually want to change the table, you can do:

delete t from t
    where col1 > col2;

This assumes that all pairs of columns are in the database.

Answer 2

When inserting or selecting, do something like this instead of col1, col2:

LEAST(col1, col2), GREATEST(col1, col2)