MySQL: Get unique values across multiple columns in alphabetical order

Question

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!

reko_t · Accepted Answer · 2010-05-10 07:30:31Z

8

(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

answered May 10, 2010 at 7:30

reko_t

56.6k10 gold badges92 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Salman Arshad Over a year ago

Using DISTINCT is optional, UNION returns distinct rows only.

Michael Buen · Accepted Answer · 2010-05-10 07:29:48Z

3

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

answered May 10, 2010 at 7:29

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

5 Comments

reko_t Over a year ago

UNION only removes duplicates shared between the unioned tables. If either of the tables includes duplicates unique to its own sub-select, those duplicates won't be removed, you need SELECT DISTINCT or GROUP BY to do that.

reko_t Over a year ago

Also not 100% sure, but I think that your ORDER BY col only applies to the last select, not the whole unioned result-set. You should use parenthesis around the select statements to clarify the purpose.

Michael Buen Over a year ago

reko_t: on the contrary, each of those select, the duplicate in each of them are also removed across all of them

reko_t Over a year ago

Ah, it seems that's the case, just tried it. Thanks for the correction.

Michael Buen Over a year ago

reko_t: regarding ORDER BY. i've been programming with SQL for long, i've used MySQL, SQL Server, Postgresql. the ORDER BY applies on the whole UNIONed set

Collectives™ on Stack Overflow

MySQL: Get unique values across multiple columns in alphabetical order

2 Answers 2

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related