CODEWITHSUNDEEP
mysqlsqldistinct
unsafe_where_true
unsafe_where_true

Reputation: 6300

Select multiple distinct columns from same table separately

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 :)

Upvotes: 1

Views: 2897

Answers (2)

Tom H
Tom H

Reputation: 47444

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.

Upvotes: 2

Rhuarc13
Rhuarc13

Reputation: 135

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.

Upvotes: -1

Related Questions