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"
What you mean is either
SELECT DISTINCT a, b FROM pairs;
or
SELECT a, b FROM pairs GROUP BY a, b;
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.
This will give you the result you're giving as an example:
SELECT DISTINCT a, b
FROM pairs
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
Source: Stackoverflow.com