How to select distinct values from 2 tables with sort in one query?

Question

I have

table1 : country, theOrderColumn1
table2 : country, theOrderColumn2

I want to join DISTINCT country from these two SELECT statements:

SELECT DISTINCT `country` FROM `table1` ORDER BY `theOrderColumn1`

and

SELECT DISTINCT `country` FROM `table2` ORDER BY `theOrderColumn2`

Example:

table1 (country, theOrderColumn1): (uk, 1), (usa, 2)
table2 (country, theOrderColumn2): (france, 1), (uk, 2)

I want this result:

france
uk
usa

D'Arcy Rittich · Answer 1 · 2017-09-13T12:30:00.727

39

select distinct country
from (
    select country, theOrderColumn from table1
    union all
    select country, theOrderColumn from table2
) a 
order by theOrderColumn

edited Sep 13 '17 at 12:30

answered Sep 28 '11 at 13:42

D'Arcy Rittich

167,292
40
290
283

score 2 · Answer 2 · answered Sep 28 '11 at 13:42

2

select country, theOrderColumn from (
select distinct t1.country as country, t1.theOrderColumn as theOrderColumn from table t1
union
select distinct t2.country as country, t2.theOrderColumn as theOrderColumn from table t2) t3
order by theOrderColumn

answered Sep 28 '11 at 13:42

Icarus

63,293
14
100
115

score 2 · Answer 3 · edited Apr 01 '21 at 10:37

If you want to preserve order given by theOrderColumn1 and theOrderColumn2 at the same time, you can use the column index to specify the ORDER BY column.

SELECT DISTINCT country FROM (
(
    SELECT country AS c, theOrderColumn1 AS d FROM table1
    UNION
    SELECT country AS c, theOrderColumn2 AS d FROM table2
) ORDER BY 2)

Take a look at the answers to this question: SQL Query - Using Order By in UNION

score 1 · Answer 4 · answered Sep 28 '11 at 13:43

1

select a.country,a.theOrderColumn
(
select country,theOrderColumn
from table1
union
select country,theOrderColumn
from table2
) a
order by a.theOrderColumn

Though you will get duplicates if theOrderColumn is different in table 1 and table 2.

answered Sep 28 '11 at 13:43

Tom Mac

9,693
3
25
35

score 1 · Answer 5 · edited Sep 28 '11 at 13:47

1

It depends on what you are wanting and how you want to join the two tables together. If you are joining based on "theOrderColumn", then the query would be

SELECT DISTINCT country 
FROM table1 
JOIN table2 ON table1.theOrderColumn = table2.theOrderColumn 
ORDER BY theOrderColumn

If you want to join across country (which wouldn't make sense as the country would be identical in both tables) then you could swap out "country" in the join clause.

Also, depending on your SQL dialect spoken by your DBMS, your mileage may vary with the above query. Can you clarify more of what you are after?

edited Sep 28 '11 at 13:47

marc_s

732,580
175
1,330
1,459

answered Sep 28 '11 at 13:45

Robert Lowe

51
1

1

If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Sep 28 '11 at 13:47

How to select distinct values from 2 tables with sort in one query?

5 Answers5