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
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.
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