110

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

Improve this question
7
  • if it has any unique column, this will not work. distinct does not work on unique column. Commented Aug 29, 2012 at 23:45
  • None of the columns are unique Commented Aug 29, 2012 at 23:46
  • then you are getting the correct result as long as you are not using join Commented Aug 29, 2012 at 23:46
  • 4
    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. Commented Aug 29, 2012 at 23:47
  • 2
    I want distinct values of a's, b's c's and d's, not combination of values Commented Aug 29, 2012 at 23:50

10 Answers 10

Reset to default
128

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Kinda cool. The 'group by' forces the distinct-ness. Is that what is happening?
Could you please elaborate a little more the query?
@ncastro Apologies, but this answer does not satisfy the question.
This one is slow for large tables.
Came here for that. I have a query that takes nearly a second, and without the grouping it takes 3ms.
62

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
Improve this answer

9 Comments

What if I need only distinct results from first two column but need to show all columns...
ncastro's is better. Cooler, easier, anyway.
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
But this result gives comma separated value. How can I get rid of this problems.
Chayan, that's a different issues, but it's REPLACE(group_concat(DISTINCT d),',',' ')
|
27

Another simple way to do it is with concat()

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

5 Comments

Sorry, But I cannot find any equivalent in Derby Database.
This is clever and it's exactly what I needed
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
Very slow for large tables.
This one is really clever.
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
Improve this answer

4 Comments

This works too, but I like the separation between the results better in Nesim Razon's answer. Thank you
Cool - I was going to look at combining them
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
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'
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
Improve this answer

2 Comments

Nice example that solved my problem easily. Thanks.
It will give me the values in the single column what if I want to show them separately ?
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.

Improve this answer

4 Comments

I want disctinct values of a's, b's, c's and d's, not distinct combination of values
@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.
@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.
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
5

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

Improve this answer

2 Comments

This answer was reviewed in the Low Quality Queue. Here are some guidelines for How do I write a good 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
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
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.

Improve this answer

Comments

0

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...

Improve this answer

Comments

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.