CODEWITHSUNDEEP
mysqlsqlselect
Ivan
Ivan

Reputation: 64227

How to address multiple columns as one in MySQL?

Columns a, b and c contain some values of the same nature. I need to select all the unique values. If I had just one column I'd use something like

SELECT DISTINCT a FROM mytable ORDER BY a;

but I need to treat a, b and c columns as one and gett all the unique values ever occurring among them.

As an example, let this be a CSV representation of mytable, the first row naming the columns:

a, b, c
1, 2, 3
1, 3, 4
5, 7, 1

The result of the query is to be:

1
2
3
4
5
7

UPDATE: I don't understand why do all of you suggest wrapping it in an extra SELECT? It seems to me that the answer is

(SELECT `a` AS `result` FROM `mytable`)
UNION (SELECT `b` FROM `mytable`)
UNION (SELECT `c` FROM `mytable`)
ORDER BY `result`;

isn't it?

Upvotes: 4

Views: 3236

Answers (4)

Mosty Mostacho
Mosty Mostacho

Reputation: 43484

So you want one column all with unique values from a, b and c? Try this:

(select a as yourField from d1)
union
(select b from d2)
union
(select c from d3)
order by yourField desc
limit 5

Working example

Edited after requirements changed... There you have the order by and limit you requested. Of course, you'll get only 5 records in this example

Upvotes: 6

John Woo
John Woo

Reputation: 263813

try this:

SELECT b.iResult
FROM
    (SELECT a as iResult FROM tableName
        UNION
    SELECT b as iResult FROM tableName
        UNION
    SELECT c as iResult FROM tableName) b
ORDER BY b.iResult 
LIMIT BY 10 -- or put any number you want to limit.

Upvotes: 2

mirza
mirza

Reputation: 5793

SELECT tmp.a
FROM 
(SELECT column_1 AS a
FROM table
UNION
SELECT column_2 AS a
FROM table
UNION
SELECT column_3 AS a
FROM table) AS tmp
ORDER BY `tmp`.`a` ASC

Upvotes: 2

AJP
AJP

Reputation: 2125

sorry i miss understood your question. here is updated query. 

select a from my table
UNION
select b from my table
UNION
select c from my table

Upvotes: 6

Related Questions