0

This is a slightly strange use case admittedly, but how do you order the results of a simple union like this alphabetically?

select name 
from Reviewer
union
select model
from Car;
RDJ
  • 4,052
  • 9
  • 36
  • 54
  • 1
    dupe: http://stackoverflow.com/questions/4715820/how-to-order-by-with-union – Jeremy Aug 06 '15 at 11:40
  • @Jeremy Agreed. When I was researching, that particular question didn't come up, possibly due to my focus on the term 'alphabetical'. – RDJ Aug 06 '15 at 11:56

5 Answers5

6

Ironically, in many databases, your query would return the values in alphabetical order. The sorting is done during the duplicate removal for union.

Of course, you can't depend on that. So, you should include an order by clause:

select name 
from Reviewer
union
select model
from Car
order by name;

The order by applies to the complete union. The column name comes from the first subquery.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Most DBMS I know would use a hashing algorithm to get rid of the duplicates, so no "sorting" would be done. –  Aug 06 '15 at 11:41
  • Does the 2 aliases in union must be the same? (or at least in SQL Server) – Bogdan Bogdanov Aug 06 '15 at 11:42
  • @Gordon I'm familiar with `order by`, just didn't realise it could be applied in that way across the whole union. Seems somewhat counter intuitive, but very useful all the same. – RDJ Aug 06 '15 at 12:02
  • 1
    @Jonathan it's not really best practice, but you can also use "order by 1" to order by the first field in the resultset. – Jeremy Aug 06 '15 at 12:22
3
select val
from (
  select name as val 
  from Reviewer

  union

  select model as val
  from Car
) as x
order by val
Praveen
  • 8,945
  • 4
  • 31
  • 49
1

Here is how to set alias and ORDER BY:

SELECT 
  name AS NameModel
FROM Reviewer
UNION
SELECT  
  model AS NameModel
from Car
ORDER BY 
 NameModel;

The ORDER BY is evaluated after UNION.

Bogdan Bogdanov
  • 1,707
  • 2
  • 20
  • 31
1

your sql : 

select name 
from Reviewer
union
select model
from Car;

Answer 1 :

select name from Reviewer
union
select model from Car
order by name;

Answer 2 :

select * from
    (select name "id" from Reviewer
    union
    select model from Car) x
order by x.id;
Re Captcha
  • 3,125
  • 2
  • 22
  • 34
CHINTU RANA
  • 87
  • 6
0
select val
from (
  select name as val 
  from Reviewer

  union

  select model as val
  from Car
) as x
order by val

My answer and above both are same. In SQL, we can write in different ways.

We can also ignore as operator.

TryinHard
  • 4,078
  • 3
  • 28
  • 54
CHINTU RANA
  • 87
  • 6