MYSQL select DISTINCT values in two columns

Question

I want to select distinct values in a database. Let me run you through a quick example.

Table:

foo bar
--- ---
a   c
c   f
d   a
c   a
f   c
a   c
d   a
a   c
c   a
f   c

Right, let's say my SQL is SELECT DISTINCT foo, bar from table. These are my results:

foo bar
--- ---
a   c
c   f
d   a
c   a
f   c

However the problem is is that there are repetitions of a c / c a just that they are in a different order. I don't want to select these, I want distinct values from both columns, please help!

Is this a question that you have been asked in some interview. :P — Talha Ahmed Khan, Jul 19 '11 at 09:13

score 98 · Answer 1 · answered Apr 05 '13 at 15:18

98

How about using GROUP BY?

SELECT foo,bar FROM my_table GROUP BY foo,bar

answered Apr 05 '13 at 15:18

Yaniv

1,103
7
2

4

This is by far the better solution! – Dominique Feb 20 '15 at 16:17
3

Absolutely a much better answer. – CB. Jul 02 '15 at 15:32
34

How could it be? This is not a right answer. Grouping just [foo=A, bar=C] and [foo=C, bar=A] by "foo,bar" will result in TWO rows. While OP wants single row in this case. – Reloecc May 03 '16 at 16:31

score 50 · Accepted Answer · edited Jul 19 '11 at 11:16

50

very very wicked & evil:

select distinct
    least(foo, bar) as value1
  , greatest(foo, bar) as value2
from table

edited Jul 19 '11 at 11:16

ypercubeᵀᴹ

113,259
19
174
235

answered Jul 19 '11 at 09:12

nabuchodonossor

2,095
20
18

5

This request is evil. foo can be in value1 as well as in value2. (Just tested it) – UnstableFractal Mar 21 '13 at 09:42
Doesn't work properly. My first select returned a tuple such as `(5, 108)` - the second number would be such big of an aberration, I had to check the record was really there. I ran a select and it wasn't. – Buffalo May 18 '17 at 08:50
I have a solution for this problem Buffalo. https://stackoverflow.com/a/50848228/8883361 – Yusuf Çağlar Jun 14 '18 at 01:06

Kevin Burton · Answer 3 · 2019-11-14T14:05:35.977

7

How about :

SELECT DISTINCT a.foo,a.bar 
FROM table a
LEFT JOIN table b ON a.foo=b.bar and a.bar=b.foo
WHERE b.foo IS NULL AND b.bar IS NULL

Output :

foo bar
--- ---
d   a

edited Nov 14 '19 at 14:05

answered Jul 19 '11 at 09:17

Kevin Burton

11,676
2
24
37

@KevinBurton `foo` and `bar` in `WHERE` clause are ambiguous – VB_ Mar 26 '15 at 14:06

outis · Answer 4 · 2011-07-19T09:59:42.373

4

You're asking for something that's the opposite of a symmetric closure (I don't know if it has a special name; antisymmetric something, since it's not a closure). For closures and closure like things where you need to compare two different columns, you can use joins. To make sure you don't filter out both rows when they are duplicated across columns, you need to a way to differentiate the repeats and include one of them, such as by including the pair where the first is the lesser.

SELECT DISTINCT t1.foo, t1.bar
  FROM `table` t1
    LEFT JOIN `table` t2
      ON t1.foo=t2.bar AND t1.bar=t2.foo 
  WHERE t2.foo IS NULL OR t1.foo <= t1.bar;

edited Jul 19 '11 at 09:59

answered Jul 19 '11 at 09:44

outis

75,655
22
151
221

I'm trying to do something similar to the OP. Is there a link to a good tutorial and explanation available? Since we're not sure on the terminology I'm struggling to locate on on the net. – djskinner Nov 23 '12 at 15:40

ypercubeᵀᴹ · Answer 5 · 2012-06-08T07:42:24.223

4

   SELECT 
       foo, bar
   FROM tableX
   WHERE foo <= bar
 UNION 
   SELECT 
       bar, foo
   FROM tableX
   WHERE bar < foo

edited Jun 08 '12 at 07:42

answered Jul 19 '11 at 11:24

ypercubeᵀᴹ

113,259
19
174
235

score 3 · Answer 6 · answered Jul 19 '11 at 09:17

3

SELECT DISTINCT foo, bar FROM table WHERE
CONCAT(',',foo,bar,) NOT IN ( SELECT CONCAT(',',bar,foo) FROM table )

answered Jul 19 '11 at 09:17

Talha Ahmed Khan

15,043
10
42
49

score 1 · Answer 7 · answered Nov 24 '13 at 18:28

1

This works for me:

SELECT DISTINCT
LEAST(sub.foo, sub.bar) as value_1
, GREATEST(sub.foo, sub.bar) as value_2

FROM
(SELECT
a.foo
,a.bar
FROM
table a
JOIN
table b
on a.foo = b.bar
and a.bar = b.foo) sub

answered Nov 24 '13 at 18:28

bfortuner

11
1

score 0 · Answer 8 · edited May 20 '20 at 18:24

So what I understood you want to combine 2 fields into a single SQL search result rows... I found most answers here very confusing.

This is the way I always do it when I have to (can be scaled up with multiple rows and multiple tables):

SELECT a.field1 FROM (<br>
&nbsp;&nbsp;&nbsp;&nbsp;SELECT foo AS field1 FROM tableX<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UNION<br>
&nbsp;&nbsp;&nbsp;&nbsp;SELECT bar AS field1 FROM tableX<br>
) a

OR

SELECT DISTINCT a.field1 FROM (<br>
&nbsp;&nbsp;&nbsp;&nbsp;SELECT foo AS field1, conditions FROM tableX<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UNION ALL<br>
&nbsp;&nbsp;&nbsp;&nbsp;SELECT bar AS field1, conditions FROM tableX<br>
) a WHERE a.conditions=1 (added extra conditions to filter out some results)

MYSQL select DISTINCT values in two columns

8 Answers8

Linked

Related