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"

score 112 · Accepted Answer · edited Aug 25 '09 at 21:33

112

What you mean is either

SELECT DISTINCT a, b FROM pairs;

or

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

edited Aug 25 '09 at 21:33

OscarRyz

196,001
113
385
569

answered Aug 25 '09 at 20:16

Michael Krelin - hacker

138,757
24
193
173

3

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 – JamesMLV Aug 25 '09 at 20:17
6

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 Aug 25 '09 at 20:22
Oscar now I realized that my query will give you extra column for b=1 (I actually misread it, knowing that you want both columns, I assumed you want distinct rows)… – Michael Krelin - hacker Aug 25 '09 at 20:26
And here is a couple of more queries for you ;-) – Michael Krelin - hacker Aug 25 '09 at 20:29
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 Aug 25 '09 at 21:01
Hmm.. if you have *given value of column B*, then I'll add another query to the list now ;-) – Michael Krelin - hacker Aug 25 '09 at 21:02
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 Aug 25 '09 at 21:07
Still sounds like distinct pairs. And now you can add `INSERT INTO pairs VALUES ('SQLand','SQL')` – Michael Krelin - hacker Aug 25 '09 at 21:12
oops, columns are numeric ;-) – Michael Krelin - hacker Aug 25 '09 at 21:13
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 Aug 25 '09 at 21:26
Looks like someone removed his comments… ;-) – Michael Krelin - hacker Nov 09 '11 at 20:34

score 22 · Answer 2 · answered Feb 14 '14 at 16:42

22

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

answered Feb 14 '14 at 16:42

StuartQ

3,739
1
26
23

1

of course! however i was stumped on how to do this before i read this answer. thx. – Greg Nov 26 '16 at 16:21

score 12 · Answer 3 · answered Aug 25 '09 at 20:16

12

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

SELECT DISTINCT a, b
FROM pairs

answered Aug 25 '09 at 20:16

Lasse V. Karlsen

380,855
102
628
825

score 5 · Answer 4 · answered Jan 28 '12 at 21:11

5

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.

answered Jan 28 '12 at 21:11

meszias

91
1
4

1

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? – Chris Jul 11 '12 at 17:46

score -2 · Answer 5 · answered Jul 14 '13 at 13:27

-2

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.

answered Jul 14 '13 at 13:27

adam

293
4
6

Distinct pair of values SQL

5 Answers5

Linked