MYSQL select DISTINCT values in two columns
MysqlSqlMysql Problem Overview
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!
Mysql Solutions
Solution 1 - Mysql
How about using GROUP BY?
SELECT foo,bar FROM my_table GROUP BY foo,bar
Solution 2 - Mysql
very very wicked & evil:
select distinct
least(foo, bar) as value1
, greatest(foo, bar) as value2
from table
Solution 3 - Mysql
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
Solution 4 - Mysql
SELECT
foo, bar
FROM tableX
WHERE foo <= bar
UNION
SELECT
bar, foo
FROM tableX
WHERE bar < foo
Solution 5 - Mysql
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;
Solution 6 - Mysql
SELECT DISTINCT foo, bar FROM table WHERE
CONCAT(',',foo,bar,) NOT IN ( SELECT CONCAT(',',bar,foo) FROM table )
Solution 7 - Mysql
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
Solution 8 - Mysql
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>
SELECT foo AS field1 FROM tableX<br>
UNION<br>
SELECT bar AS field1 FROM tableX<br>
) a
OR
SELECT DISTINCT a.field1 FROM (<br>
SELECT foo AS field1, conditions FROM tableX<br>
UNION ALL<br>
SELECT bar AS field1, conditions FROM tableX<br>
) a WHERE a.conditions=1 (added extra conditions to filter out some results)