MySQL: Get unique values across multiple columns in alphabetical order

Question

Hey everyone,

If my table looks like this:

id | colA   | colB | colC
===========================
1  | red    | blue | yellow
2  | orange | red  | red
3  | orange | blue | cyan

What SELECT query do I run such that the results returned are:

blue, cyan, orange, red, yellow

Basically, I want to extract a collective list of distinct values across multiple columns and return them in alphabetical order.

I am not concerned with performance optimization, because the results are being parsed to an XML file that will serve as a cache (database is hardly updated). So even a dirty solution would be fine.

Thanks for any help!

Answer 1

Just do it the normal way:

create table new_tbl(col varchar(50));


insert into new_tbl(col)
select cola from tbl
union
select colb from tbl
union
select colc from tbl

Then sort:

select col from new_tbl order by col

Or if you don't want staging table, just do:

select cola as col from tbl
union
select colb from tbl
union
select colc from tbl
order by col

Note: UNION automatically remove all duplicates, if you want to include duplicates, change the UNION to UNION ALL

Answer 2

(SELECT DISTINCT colA AS color FROM table) UNION
(SELECT DISTINCT colB AS color FROM table) UNION
(SELECT DISTINCT colC AS color FROM table)
ORDER BY color