CODEWITHSUNDEEP
mysqlsqlsqlite
eciusr
eciusr

Reputation: 163

Trying to get the DISTINCT values of 3 columns in sqlite db

SO I have a table with 3 cols:

Col1   Col2   Col3
 a       b     c
 b       c    null
 a      null   b
 c       d     a

And my desired output will be:

a,b,c,d,null

I am hoping to have the output in a single string if possible.

I have tried:

SELECT DISTINCT col1, col2, col3 FROM table

and didn't get the desired results. Any Ideas?

Upvotes: 0

Views: 109

Answers (4)

fthiella
fthiella

Reputation: 49049

If you are using MySql, you could use this solution:

select group_concat(coalesce(c,'null') order by c is null, c)
from (
  select col1 c from tbl
  union
  select col2 c from tbl
  union
  select col3 c from tbl
) u

Union query selects all values, removing all duplicates. I'm then returning the result in a single string, ordered by value with null value at the end, and converting null to 'null' (since group_concat would ignore null values).

If you are using SQLite, Group_Concat doesn't support order by, and you could use this:

select group_concat(coalesce(c,'null'))
from (
  select col1 c, col1 is null o from mytable
  union
  select col2 c, col2 is null o from mytable
  union
  select col3 c, col3 is null o from mytable
  order by o, c
) u

Upvotes: 0

user359135
user359135

Reputation:

does this work in sqlite:

select col1 from table 
union
select col2 from table
union 
select coll3 from table

or:

select col1 from table where col1 is not null
union
select col2 from table where col2 is not null
union 
select coll3 from table where col3 is not null

to eliminate nulls.

Note i don't think this would be fast to execute but I know in mssql union will do a distinct on the results

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425371

A single-string solution (see on sqlfiddle):

SELECT  GROUP_CONCAT(COALESCE(c, 'NULL'), ',')
FROM    (
        SELECT  col1 c
        FROM    mytable
        UNION
        SELECT  col2 c
        FROM    mytable
        UNION
        SELECT  col3 c
        FROM    mytable
        ) q

Upvotes: 2

Dale M
Dale M

Reputation: 2473

SELECT Col1
FROM table
UNION
SELECT Col2
FROM table
UNION
SELECT Col3
FROM table

Upvotes: 1

Related Questions