Distinct pair of values SQL
SqlGroup ByDistinctSql Problem Overview
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"
Sql Solutions
Solution 1 - Sql
What you mean is either
SELECT DISTINCT a, b FROM pairs;
or
SELECT a, b FROM pairs GROUP BY a, b;
Solution 2 - Sql
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
Solution 3 - Sql
This will give you the result you're giving as an example:
SELECT DISTINCT a, b
FROM pairs
Solution 4 - Sql
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.
Solution 5 - Sql
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.