CODEWITHSUNDEEP
sqlgroup-bydistinct
OscarRyz
OscarRyz

Reputation: 199234

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"

Upvotes: 82

Views: 160306

Answers (5)

StuartQ
StuartQ

Reputation: 3809

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

Upvotes: 23

adam
adam

Reputation: 303

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.

Upvotes: -2

meszias
meszias

Reputation: 101

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.

Upvotes: 5

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143099

What you mean is either

SELECT DISTINCT a, b FROM pairs;

or

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

Upvotes: 116

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391396

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

SELECT DISTINCT a, b
FROM pairs

Upvotes: 12

Related Questions