A clear pair of SQL values

Question

Consider

create table pairs ( number a, number b ) 

If the data

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

Etc.

Which query gives me the different values ​​that the column of b has. So I can see

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

only

I tried

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

but gives me "not a group by expression"

Answer 1

What do you mean is either

 SELECT DISTINCT a, b FROM pairs; 

or

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

Answer 2

If you want to process 1,2 and 2,1 as the same pair, this will give you a unique list in 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 answered above

Answer 3

This will give you the result you give as an example:

 SELECT DISTINCT a, b FROM pairs 

Answer 4

if you want to filter tuples you can use as follows:

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

Good stuff - you don’t need to use a band.

Answer 5

If you just want to count individual pairs.

The easiest way to do this is as follows: SELECT COUNT(DISTINCT a,b) FROM pairs

The previous solutions will list all the pairs, and then you will have to make a second query to count them.