MySQL: How to get not repeated values?

Question

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

Will there always only be 2 rows for col 1? if not this is a Pivot. and there's several examples on this on stack already. For example: https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql — xQbert, Oct 04 '21 at 20:08

score 1 · Accepted Answer · answered Oct 04 '21 at 20:06

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.

answered Oct 04 '21 at 20:06

Shadow

1 Answers1