CODEWITHSUNDEEP
sql
RDJ
RDJ

Reputation: 4122

SQL: How to order a union alphabetically?

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;

Upvotes: 0

Views: 1468

Answers (5)

CHINTU RANA
CHINTU RANA

Reputation: 97

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.

Upvotes: 0

CHINTU RANA
CHINTU RANA

Reputation: 97

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;

Upvotes: 1

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

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.

Upvotes: 1

Praveen
Praveen

Reputation: 9345

select val
from (
  select name as val 
  from Reviewer

  union

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270181

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.

Upvotes: 6

Related Questions