MySQL: How to get not repeated values?

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0