select distinct values from all columns

Question

I want to select only distinct values from each column. Not distinct combination. Here is my query but its returning distinct combination of all columns. i want that each column show its own distinct values without depending on other columns.

 $sql= "select distinct
            CASE WHEN col1 not regexp '[0-9]' THEN col1 else null end as col1
            ,CASE WHEN col2 not regexp '[0-9]' THEN col2 else null end as col2
            ,CASE WHEN col3 not regexp '[0-9]' THEN col3 else null end as col3 from table_name";

It returns this:

col1| col2| col3
a      1     3
b      2     4
c      2     4
d      1     3

you guys could see that the values of col2 and col3 are not distinct i want only distinct from col2 and col3 too. Thanks!

Can you elaborate on but not working? Can you show us sample data and what your desired output is? — Tim Biegeleisen
Distinct works across all columns, not individually in them, in the case of distinct col1, col2, col3 ... and your case stmt does not impact that — Drew
Sample data and desired results would help us understand what you want. — Gordon Linoff
@Drew so i want to select distinct values of all col1, col2, col3 separately. not the distinct combination of all these columns. — tooba

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-07-23T15:52:10

My best guess as to what you want is to use union all:

select distinct (case when col1 not regexp '[0-9]' then col1 end) as col1, 'col1' as which
from table_name
union all
select distinct (case when col2 not regexp '[0-9]' then col2 end) as col2, 'col2' as which
from table_name
union all
select distinct (case when col3 not regexp '[0-9]' then col3 end) as col3, 'col3' as which
from table_name;

This produces a result with two columns, one is the column name and the other is a distinct value in the column.

select distinct values from all columns

1 Answers