There is MySQL table:

col1   col2
1       a 
1       b
2       c
2       d

The wished result as:

  col1    col2  col3 
    1        a      b 
    2        c      d

I did :

select t1.col1, t1.col2 , t2.col2 from
my_table t1 left join my_table t2  
on t1.col1 = t2.col1
where t1.col2 <> t2.col2

But i get 4 rows

col1   col2  col3
1       b     a
1       a     b
2       c     d
2       d     c

CodePudding user response：

As long as you do not have completely duplicate rows, just change your where clause to where t1.col2 < t2.col2.

This will make sure that you can get

1 a b

but you will not get

1 b a

row.