MySQL SELECT DISTINCT multiple columns

Question

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

if it has any unique column, this will not work. distinct does not work on unique column. — Hammad Khan, Aug 29 '12 at 23:45
then you are getting the correct result as long as you are not using join — Hammad Khan, Aug 29 '12 at 23:46
@Thecrocodilehunter Of course it would work - why would distinct not work on a unique column, — Adrian Cornish, Aug 29 '12 at 23:46
You should explain what you mean by "distinct values of ALL of these 4 columns", as it must mean something different to you than it usually means for those queries to not return what you want. Sample data and sample output would be a concise way of conveying that. — Dan Grossman, Aug 29 '12 at 23:47
Do you want all the distinct a's b's, c's and d's or the distinct combination of the values - your 1st query should work — Adrian Cornish, Aug 29 '12 at 23:47
I want distinct values of a's, b's c's and d's, not combination of values — user765368, Aug 29 '12 at 23:50

score 104 · Answer 1 · edited Jul 06 '21 at 23:34

104

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.

edited Jul 06 '21 at 23:34

informatik01

15,636
10
72
102

answered Oct 20 '15 at 13:06

Nicolas Castro

1,283
1
12
11

2

Kinda cool. The 'group by' forces the distinct-ness. Is that what is happening? – Jibran Dec 03 '15 at 04:50
Could you please elaborate a little more the query? – Jorge Lavín Jan 15 '16 at 12:21
12

@ncastro Apologies, but this answer does not satisfy the question. – Knowledge Craving Feb 17 '16 at 02:25
This one is slow for large tables. – John T Nov 26 '20 at 05:24

score 59 · Accepted Answer · answered Aug 29 '12 at 23:56

59

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

answered Aug 29 '12 at 23:56

Nesim Razon

9,384
2
32
47

9

What if I need only distinct results from first two column but need to show all columns... – Uday Hiwarale Dec 17 '13 at 11:32
2

ncastro's is better. Cooler, easier, anyway. – Buffalo Aug 30 '16 at 08:24
Hey Nesim, Thanks! by the way if "my_table" is a whole query how can I call it by name and recall it again and again – Tomer Apr 03 '17 at 18:52
But this result gives comma separated value. How can I get rid of this problems. – Chayan Biswas Aug 04 '17 at 21:09
Chayan, that's a different issues, but it's `REPLACE(group_concat(DISTINCT d),',',' ')` – Slam Oct 06 '17 at 18:10
Mysql Error as : #1247 - Reference 'a' not supported (forward reference in item list). – bansal Jun 18 '18 at 11:36
Note that this will silently truncate each resulting column to `group_concat_max_len` characters long, [which by default](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_group_concat_max_len) is only 1024 characters - you could lose data! – thenickdude Jun 20 '18 at 02:34
distinct results from first tow rows `SELECT (SELECT GROUP_CONCAT(a) FROM (SELECT DISTINCT a FROM my_table LIMIT 2) AS a) AS a, (SELECT GROUP_CONCAT(a) FROM (SELECT DISTINCT b FROM my_table LIMIT 2) AS b) AS b;` – free斩 Jun 24 '20 at 16:00

score 22 · Answer 3 · answered Aug 29 '12 at 23:55

22

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

answered Aug 29 '12 at 23:55

Adrian Cornish

22,083
11
55
77

1

This works too, but I like the separation between the results better in Nesim Razon's answer. Thank you – user765368 Aug 30 '12 at 00:00
Cool - I was going to look at combining them – Adrian Cornish Aug 30 '12 at 00:05
5

If you want DISTINCT values across all the columns, you have to wrap this query in another SELECT statement like this: `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` – T. Brian Jones Mar 04 '15 at 22:31
`SELECT e,f FROM ( SELECT DISTINCT zdjh AS e FROM `tj_xhqd` UNION SELECT DISTINCT sjsj AS f FROM `tj_xhqd` UNION SELECT DISTINCT xhqd AS g FROM `tj_xhqd`) a` gives me `Error Code: 1054 Unknown column 'f' in 'field list'` – Moeez Jul 04 '18 at 07:14

score 22 · Answer 4 · edited Sep 30 '15 at 08:59

22

Another simple way to do it is with concat()

SELECT DISTINCT(CONCAT(a,b)) AS cc FROM my_table GROUP BY (cc);

edited Sep 30 '15 at 08:59

Tamir Vered

9,950
5
44
56

answered Sep 30 '15 at 08:29

Kelhen

221
2
7

Sorry, But I cannot find any equivalent in Derby Database. – Pranjal Choladhara May 01 '16 at 18:39
This is clever and it's exactly what I needed – xtian Mar 24 '19 at 17:48
This is what worked for me. I didn't know how to use the other answers with the multiple joins I have in the query – murume Apr 17 '20 at 13:25
Very slow for large tables. – John T Nov 26 '20 at 05:26

score 9 · Answer 5 · answered Aug 29 '12 at 23:50

9

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.

answered Aug 29 '12 at 23:50

Hammad Khan

15,376
14
106
131

I want disctinct values of a's, b's, c's and d's, not distinct combination of values – user765368 Aug 29 '12 at 23:51
@hmd: Thanks for this solution. Exactly what I needed. Is there is way to include a `where` condition? I wanted only rows where the count was greater than 1. Tried `select distinct a,b,c,count(*) from MyTempTable group by a,b,c order by count(*) desc where count(*)>1;` but it didn't work. – Nav Jun 01 '16 at 09:53
@Nav42 you need to use `having count(*) > 1` not `where` as this is aggregate query. Btw my answer does not exactly addresses the OP question as he is outlined above. Anyways glad to help. – Hammad Khan Jun 01 '16 at 10:36
Works. Thank you very much. select `distinct a,b,c,count(*) from MyTable group by a,b,c having count(*) > 1 order by count(*) desc ` – Nav Jun 02 '16 at 05:12

score 9 · Answer 6 · answered Mar 04 '15 at 22:33

9

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

answered Mar 04 '15 at 22:33

T. Brian Jones

12,350
22
73
112

Nice example that solved my problem easily. Thanks. – delliottg Sep 17 '15 at 15:07
2

It will give me the values in the single column what if I want to show them separately ? – Moeez Jul 04 '18 at 07:24

MySQL SELECT DISTINCT multiple columns

6 Answers6

Linked