CODEWITHSUNDEEP
sqlgroup-byhivehiveql
Badr Ftillou
Badr Ftillou

Reputation: 59

Select Distinct pair of column values

I have a table like this

A1 | A2
a  | b
c  | d
b  | a
a  | b

And I want to select distinct pairs :

A1 | A2
a  | b
c  | d

I tried :

select a, b from (
select a, b , a|b as ab, b|a as ba from T
)t where ab!=ba group by a, b

Anyone have a better idea about how I can do this ? Thanks

Upvotes: 0

Views: 4958

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

This would be the cleanest way if NULL values are not involved

select  distinct
        least    (A1,A2) as A1
       ,greatest (A1,A2) as A2

from    t
;

+-----+-----+
| a1  | a2  |
+-----+-----+
| a   | b   |
| c   | d   |
+-----+-----+

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

An ANSI compliant way of doing this would be to rearrange each pair of A1 and A2 values as min/max using CASE expressions. Then just select distinct on this derived table.

SELECT DISTINCT
    A1, A2
FROM
(
    SELECT
        CASE WHEN A1 < A2 THEN A1 ELSE A2 END AS A1,
        CASE WHEN A1 < A2 THEN A2 ELSE A1 END AS A2
    FROM yourTable
) t

Upvotes: 1

Related Questions