MySQL SELECT DISTINCT multiple columns
MysqlSqlDistinctMysql Problem Overview
Let's say I have columns a, b c, d
in a table in a MySQL database. What I'm trying to do is to select the distinct values of ALL of these 4 columns in my table (only the distinct values). I tried stuff like:
SELECT DISTINCT a,b,c,d FROM my_table;
SELECT DISTINCT a,b,c,d FROM my_table GROUP BY a,b,c,d;
None of those worked. Can anybody help out here?
Thank you
NOTE I want the distinct values of the columns a, b, c d
separately. Not the distinct combination of values
Mysql Solutions
Solution 1 - Mysql
I know that the question is too old, anyway:
SELECT a, b
FROM mytable
GROUP BY a, b;
This will give your all the combinations.
Solution 2 - Mysql
can this help?
select
(SELECT group_concat(DISTINCT a) FROM my_table) as a,
(SELECT group_concat(DISTINCT b) FROM my_table) as b,
(SELECT group_concat(DISTINCT c) FROM my_table) as c,
(SELECT group_concat(DISTINCT d) FROM my_table) as d
Solution 3 - Mysql
Taking a guess at the results you want so maybe this is the query you want then
SELECT DISTINCT a FROM my_table
UNION
SELECT DISTINCT b FROM my_table
UNION
SELECT DISTINCT c FROM my_table
UNION
SELECT DISTINCT d FROM my_table
Solution 4 - Mysql
Another simple way to do it is with concat()
SELECT DISTINCT(CONCAT(a,b)) AS cc FROM my_table GROUP BY (cc);
Solution 5 - Mysql
Both your queries are correct and should give you the right answer.
I would suggest the following query to troubleshoot your problem.
SELECT DISTINCT a,b,c,d,count(*) Count FROM my_table GROUP BY a,b,c,d
order by count(*) desc
That is add count(*) field. This will give you idea how many rows were eliminated using the group command.
Solution 6 - Mysql
This will give DISTINCT values across all the columns:
SELECT DISTINCT value
FROM (
SELECT DISTINCT a AS value FROM my_table
UNION SELECT DISTINCT b AS value FROM my_table
UNION SELECT DISTINCT c AS value FROM my_table
) AS derived