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 retu开发者_运维百科rned 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!
(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
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
精彩评论