Select distinct values of two columns as single

Question

For starters: yes, I've seen there are lots of questions on combining two/multiple columns - but so far I neither found the answer to my question nor something to get me started trying further (which I did).

I have: a table consisting of two INT fields.
I want: a single column holding the distinct values of both fields.

Examle table:

| a | b |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 1 | 7 |
| 2 | 3 |
| 3 | 5 |
| 5 | 7 |

My desired result would in that case be:

| 1 |
| 2 |
| 3 |
| 5 |
| 7 |

Can this be done with a single query? What would that look like?

// EDIT: some side info (which I think is irrelevant, however, I don't want to hide it):
b is always greater than a.

score 6 · Accepted Answer · answered Jun 21 '16 at 10:53

6

SELECT a AS result
FROM tableX

UNION DISTINCT

SELECT b 
FROM tableX ;

answered Jun 21 '16 at 10:53

ypercubeᵀᴹ

97,895
13
214
305

Thanks. That was fast. Does this mean there is no solution with only a single SELECT? It's only a single query, yes. I should've written SELECT in my question, though. :) – tfrommen Jun 21 '16 at 10:55
I don't see why one would want this weird requirement (a single SELECT keyword in the query). But no, I don't think that is possible. – ypercubeᵀᴹ Jun 21 '16 at 10:57
UNION DISTINCT gives an incorrect result. Just UNION seems correct. Any idea on this? – tfrommen Jun 21 '16 at 11:04
UNION and UNION DISTINCT should give you the same exact result. What result did you get? – ypercubeᵀᴹ Jun 21 '16 at 11:12
1 1 1 2 3 5 - instead of 1 2 3 5 7. – tfrommen Jun 21 '16 at 11:15
With UNION DISTINCT? No way. – ypercubeᵀᴹ Jun 21 '16 at 11:16
https://i.imgur.com/lftL7v2.png VS https://i.imgur.com/SC74SnW.png – tfrommen Jun 21 '16 at 11:17
Are you sure this is not a result from a previous query? Try it again and confirm please. This should not happen. And which version of MySQL do you use? – ypercubeᵀᴹ Jun 21 '16 at 11:20
Yep. I can do this as often (wrong) as I want. :) I'm using 10.1.10-MariaDB, which in fact is not MySQL, but we are talking about plain MySQL stuff here, right? – tfrommen Jun 21 '16 at 11:23
Then this is a bug in MariaDB 10.1.10 (it should not happen in MySQL, nor in Maria). I suggest you post a bug request to MariaDB. Or download the latest version, they may have fixed it already. – ypercubeᵀᴹ Jun 21 '16 at 11:25
1

@tfrommen: Just to confirm that this should work in MySQL: http://sqlfiddle.com/#!9/8971ab/1 – Andriy M Jun 21 '16 at 16:10

Select distinct values of two columns as single

1 Answers1