Tutorial :2-column distinct?


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.

Thoughts? Thanks in advance.


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  


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  


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
Next Post »