Distinct pair of values SQL

Question

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"

Solution 1

What you mean is either

SELECT DISTINCT a, b FROM pairs;

or

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

Solution 2

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

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

SELECT DISTINCT a, b
FROM pairs

Solution 4

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.

View more solutions

Comments

[JamesMLV]

Now that I think about it, grouping by every column is the same as not grouping by any. And you beat me by 30 seconds. +1

[Michael Krelin - hacker]

JamesMLV, grouping by every column is not the same as not grouping by any if you have duplicate rows. Consider the output of SELECT a,b,count(*) FROM pairs.

[Michael Krelin - hacker]

And here is a couple of more queries for you ;-)

[Michael Krelin - hacker]

Depends on what you want. If you want any row distinct on b, perhaps the DISTINCT ON one. If you want just distinct rows — one of the first two. If you want some particular row, based on whatever criteria you may think up, then some variant of the last one. The one I gave as example gives you distinct b values and minimal a for each. in your case that would be the 1,1 for b=1 (because 1 is minimum of 1 and 5).

[Michael Krelin - hacker]

Looking at your example, though, I think you don't have a particular value for B, so the first two examples are what you want. You want a set of distinct a, b pairs. So, first two queries.

[Michael Krelin - hacker]

Still sounds like distinct pairs. And now you can add INSERT INTO pairs VALUES ('SQLand','SQL')

[Michael Krelin - hacker]

oops, columns are numeric ;-)

[Michael Krelin - hacker]

Yeah, I based my first answer (which I maintain seems to be what you want) on common sense. But then I read carefully (what a mistake!) and noticed that you're talking about DISTINCT b only, so I came up with more potential solutions. Now that you explained in more detail it looks more and more like the initial answer (first two queries which give you identical results, but the GROUP BY can be easily extended to give you, for instance, the number of times you had to speak up for each land/language pair by adding count(*) ;-)).

[Michael Krelin - hacker]

Looks like someone removed his comments… ;-)

[Chris]

I was very hopeful of this method, but keep encountering a "ORA-00907: missing right parenthesis" error. What are the syntax rules for sorting the columns by this method?

[Greg]

of course! however i was stumped on how to do this before i read this answer. thx.