Distinct pair of values SQL

Consider

 create table pairs ( number a, number b )

Where the data is

1,1
1,1
1,1
2,4
2,4
3,2
3,2
5,1

Etc.

What query gives me the distinct values the number column b has So I can see

1,1
5,1
2,4
3,2

only

I've tried

select distinct ( a ) , b from pairs group by b

but gives me "not a group by expression"

136708 次浏览

小开

最佳答案

What you mean is either

SELECT DISTINCT a, b FROM pairs;

SELECT a, b FROM pairs GROUP BY a, b;

小开

This will give you the result you're giving as an example:

SELECT DISTINCT a, b
FROM pairs

小开

if you want to filter the tuples you can use on this way:

select distinct (case a > b then (a,b) else (b,a) end) from pairs

the good stuff is you don't have to use group by.

小开

If you just want a count of the distinct pairs.

The simplest way to do that is as follows SELECT COUNT(DISTINCT a,b) FROM pairs

The previous solutions would list all the pairs and then you'd have to do a second query to count them.

小开

If you want to want to treat 1,2 and 2,1 as the same pair, then this will give you the unique list on MS-SQL:

SELECT DISTINCT
CASE WHEN a > b THEN a ELSE b END as a,
CASE WHEN a > b THEN b ELSE a END as b
FROM pairs

Inspired by @meszias answer above