Select multiple distinct columns from same table separately

Question

Let's have this sample data:

+-------+---------+
| col1  | col2    |
+-------+---------+
|  111  | hello   |
|  222  | hello   |
|  111  | blabla  |
|  444  | there   |
|  555  | blabla  |
|  555  | there   |
+-------+---------+

I need a SQL returning distinct values for each columns separately (as feed for dropdown values for filtering). Thus the result should be:

+-------+---------+
| col1  | col2    |
+-------+---------+
|  111  | hello   |
|  222  | blabla  |
|  444  | there   |
|  555  |         |
+-------+---------+

The results need not be in this format; it's more important that I have the distinct values for easy access and iteration.

The closest I got is from here: https://stackoverflow.com/a/12188117/169252

select (SELECT group_concat(DISTINCT col1) FROM testtable) as col1, (SELECT group_concat(DISTINCT col2) FROM testtable) as col2;

This returns:

+-----------------+-------------------+
| col1            | col2              |
+-----------------+-------------------+
| 111,222,444,555 | velo,hallo,blabla |
+-----------------+-------------------+

That's pretty close, and I'll choose this one if no better solution comes up; it's not optimal as values are comma separated and I need to split the results.

I also tried:

SELECT DISTINCT from col1,col2 FROM testtable;

This returns the distint values of BOTH columns, not what I want.

Also:

select col1,col2 from testtable group by col1,col2;

which has been suggested elsewhere doesn't return what I need, but returns each column in-distinct :)

It's obvious to me that I could do two separate DISTINCT queries, but I wonder, for performance, if it's possible to do that in one single query? — transient_loop, Jan 18 '16 at 17:30
The best way is make two queries. It would be much faster then with parsing result of group_concat — Evgeny, Jan 18 '16 at 17:31

score 2 · Accepted Answer · answered Jan 18 '16 at 17:33

One problem with what you're asking is that you're expected resultset doesn't really make any sense from a relational database standpoint. Every column in a row of data should have a relationship with the other columns in the row.

The best way to approach this, IMO, is to return two result sets and process each one for each of your drop down boxes:

SELECT DISTINCT column_1 FROM My_Table

and

SELECT DISTINCT column_2 FROM My_Table

I'd also look into why you have that data in the same table to begin with if the two columns are not related. If they are related and you're trying to have a drop down for one column that then filters the items in the second drop down list then you really should return the full set of rows and let your front end application handle the filtering (and displaying unique results). Most drop down widgets should allow this kind of linking.

Yes I was aware that the two columns in the resultset, as depicted in my question, are actually at odds with standard RDMS thinking. I will use two different queries then. — transient_loop, Jan 18 '16 at 17:37

score -1 · Answer 2 · answered Jan 18 '16 at 17:32

-1

Try

Select (SELECT DISTINCT col1 from testtable) AS col1, (SELECT DISTINCT col2 from testtable) AS col2;

I think the group_concat is putting the commas into the returned table.

answered Jan 18 '16 at 17:32

Rhuarc13

135
1
9

Select multiple distinct columns from same table separately

2 Answers2