CODEWITHSUNDEEP
mysqlsqldatabase
Parth Parikh
Parth Parikh

Reputation: 260

How to find unique pairs from two columns in SQL?

i have a table"Dummy" with columns "col1 and col2".

How do i find unique pairs from(col1,col2). For example in the above table how do i get (a,b) or (b,a) only as my output, instead of both (a,b) and (b,a).

select
    distinct
    col1
    col2
from
    dummy
where
    dummy.col1 < dummy.col2
group by
    col1,
    col2;

the above query is wrong as it missed out the pair (d,c).

Wrong query

Upvotes: 5

Views: 9622

Answers (4)

sumit
sumit

Reputation: 15464

i would rather use concat and group by

SELECT 
col1,col2 
FROM 
tbl
GROUP BY CONCAT(LEAST(col1, col2), 
              GREATEST(col1, col2))

OR SIMPLY

SELECT 
    col1,col2 
    FROM 
    tbl
group by LEAST(col1, col2),GREATEST(col1, col2)

Upvotes: 3

dnoeth
dnoeth

Reputation: 60513

If you don't care if an existing combination is returned in the right order, e.g. a,b might also be returned asb,a:

SELECT DISTINCT
  CASE WHEN col1 > col2 THEN col2 ELSE col1 end, -- similar to LEAST
  CASE WHEN col1 < col2 THEN col2 ELSE col1 end  -- similar to GREATEST
FROM dummy
;

But if you must return an existing row it' more complicated:

SELECT t1.*
FROM dummy AS t1 LEFT JOIN dummy AS t2
ON  t1.col1 = t2.col2
AND t1.col2 = t2.col1
AND t1.col1 > t2.col1
WHERE t2.col1 IS NULL
;

SELECT col1,col2
FROM dummy AS t1
WHERE NOT EXISTS(
  SELECT * FROM dummy t2
  WHERE t1.col1 = t2.col2
    AND t1.col2 = t2.col1
    AND t1.col1 > t2.col1
);

Upvotes: 3

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use least and greatest.

select least(col1,col2),greatest(col1,col2) 
from tbl
group by least(col1,col2),greatest(col1,col2)

But this may return rows that aren't in the table if only one pair of (x,y) or (y,x) exists.

To avoid that, use

select least(col1,col2) as col1,greatest(col1,col2) as col2
from tbl
group by least(col1,col2),greatest(col1,col2) 
having count(*)>1
union all
select col1,col2 
from tbl
where (least(col1,col2),greatest(col1,col2)) in (select least(col1,col2) as col1,greatest(col1,col2) as col2
                                                 from tbl
                                                 group by least(col1,col2),greatest(col1,col2) 
                                                 having count(*)=1
                                                )

Upvotes: 0

Dai
Dai

Reputation: 155726

SELECT
    x,
    y
FROM
(

    SELECT
        DISTINCT
        col1 AS x,
        col2 AS y
    FROM
        dummy
    WHERE
        col1 <> col2

    UNION

    SELECT
        DISTINCT
        col1 AS y,
        col2 AS x
    FROM
        dummy
    WHERE
        col1 <> col2
)

Upvotes: 1

Related Questions