# Tutorial :2-column distinct?

### Question:

I'm trying to select two distinct numbers id1 and id2 from the following table:

tb_table1( bigint id1 bigint id2 bigint userid)

if I do

``select distinct id1, id2 from tb_table1   ``

I'll get, for example, two rows, (111, 222) and (222,111).

I only want one of those rows since I don't care which column, id1, or id2 that the result gets returned in. Basically, I want distinct pairs where order doesn't matter.

### Solution:1

It would be remiss of me to not point out that this suggests your table is not quite as normalized as it should be - what will you do when users acquire a third id? But anyway.

Using the fact that `UNION` (as opposed to `UNION ALL`) will automatically de-duplicate, you could do

``SELECT id1, id2 FROM tb_table1 WHERE id1 < id2  UNION  SELECT id2, id1 FROM tb_table1 WHERE NOT id1 < id2  ``

### Solution:2

I think there is a better solution but this should work:

``select t1.id1, t2.id2   from tb_table1 t1  inner join tb_table1 t2 on t1.id1 = t2.id2 and t1.id2 = t2.id1  where t2.id1 is null  ``

### Solution:3

would something like this be any help...

``SELECT DISTINCT CASE WHEN id1 < id2 THEN id1 ELSE id2,                  CASE WHEN id1 > id2 THEN id1 ELSE id2      FROM tb_table1;  ``

(Having seen AakashM's solution, though, I think I like it better).

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »