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. — TheTechGuy, Aug 29 '12 at 23:45
then you are getting the correct result as long as you are not using join — TheTechGuy, 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 116 · Answer 1 · edited Jul 06 '21 at 23:34

116

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

16,038
10
74
104

answered Oct 20 '15 at 13:06

Nicolas Castro

1,436
1
14
15

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
13

@ncastro Apologies, but this answer does not satisfy the question. – Knowledge Craving Feb 17 '16 at 02:25
1

This one is slow for large tables. – John T Nov 26 '20 at 05:24
Came here for that. I have a query that takes nearly a second, and without the grouping it takes 3ms. – Christophe De Troyer May 11 '23 at 11:09

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

60

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,684
3
36
48

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 25 · Answer 3 · edited Sep 30 '15 at 08:59

25

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

10,187
5
45
57

answered Sep 30 '15 at 08:29

Kelhen

251
3
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
This one is really clever. – Akmal Arzhang Oct 09 '22 at 04:18

score 22 · Answer 4 · 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

23,227
13
61
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
6

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 11 · Answer 5 · answered Mar 04 '15 at 22:33

11

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

13,002
25
78
117

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

score 9 · Answer 6 · 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

TheTechGuy

16,560
16
115
136

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. – TheTechGuy 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 4 · Answer 7 · answered Aug 27 '22 at 16:48

4

select distinct concat( colA , ' ' , colB , ' ' , colC ) from tableName;

answered Aug 27 '22 at 16:48

CWD Subs

191
1
2
7

1

This answer was reviewed in the [Low Quality Queue](https://stackoverflow.com/help/review-low-quality). Here are some guidelines for [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer). Code only answers are **not considered good answers**, and are likely to be downvoted and/or deleted because they are **less useful** to a community of learners. It's only obvious to you. Explain what it does, and how it's different / **better** than existing answers. [From Review](https://stackoverflow.com/review/low-quality-posts/32586107) – Trenton McKinney Aug 28 '22 at 15:29
It doesn't warrant more explanation, it's self-explanatory. Delete the answer if you don't like it, I added it because none of the other answers was that simple – CWD Subs Sep 03 '22 at 20:04

Ludwig · Answer 8 · 2022-07-08T08:07:24.030

0

You can simply use this to get back one row with four comma separated lists with all values.

SELECT
  GROUP_CONCAT(DISTINCT `a`) AS `as`,
  GROUP_CONCAT(DISTINCT `b`) AS `bs`,                
  GROUP_CONCAT(DISTINCT `c`) AS `cs`,                
  GROUP_CONCAT(DISTINCT `d`) AS `ds`
FROM
  `my_table`;

If you need an other separator than the comma, add the SEPARATOR option to GROUP_CONCAT.

edited Jul 08 '22 at 08:07

answered Jul 08 '22 at 07:56

Ludwig

3,580
2
20
24

score 0 · Answer 9 · answered Sep 05 '22 at 07:57

Based on your answers I made the same but for extracting a json column vars with arrays of 8 values all merged in 1 column with quotes trimed distinct values.

SELECT DISTINCT TRIM(BOTH '"' FROM T1.`vars`) FROM (
    SELECT JSON_EXTRACT(`vars`, '$[1]') AS `vars` 
         FROM my_table
         WHERE `vars` != '[]'
         UNION 
    SELECT JSON_EXTRACT(`vars`, '$[2]') AS `vars`
         FROM my_table
         WHERE `vars` != '[]'
         UNION 
    SELECT JSON_EXTRACT(`vars`, '$[3]') AS `vars`
         FROM my_table
         WHERE `vars` != '[]'
         UNION 
    SELECT JSON_EXTRACT(`vars`, '$[4]') AS `vars`
         FROM my_table
         WHERE `vars` != '[]'
         UNION 
    SELECT JSON_EXTRACT(`vars`, '$[5]') AS `vars`
         FROM my_table
         WHERE `vars` != '[]'
         UNION 
    SELECT JSON_EXTRACT(`vars`, '$[6]') AS `vars`
         FROM my_table
         WHERE `vars` != '[]'
         UNION 
    SELECT JSON_EXTRACT(`vars`, '$[7]') AS `vars`
         FROM my_table
         WHERE `vars` != '[]'
         UNION 
    SELECT JSON_EXTRACT(`vars`, '$[8]') AS `vars`
         FROM my_table
         WHERE `vars` != '[]'
) T1
WHERE `vars` IS NOT NULL
AND `vars` != 'false';

Hope it can helps...

score 0 · Answer 10 · edited Oct 30 '22 at 12:55

0

i do NOT know the values of your columns are ids as mine, so here is my solution if it fits you

SELECT DISTINCT (sender+receiver) AS smart_sum, sender, receiver 
FROM chatTable 
WHERE sender=1 OR receiver=1 
GROUP BY smart_sum

I have a chat table, sender and receiver columns represent the user_id of each, so the sum of them is actually unique.

edited Oct 30 '22 at 12:55

Transistor

193
12

answered Oct 29 '22 at 16:17

Omar N Shamali

503
5
11

MySQL SELECT DISTINCT multiple columns

10 Answers10

Linked