How to order by with union in SQL?

sql sql-order-by union
Guilgamos picture Guilgamos · Jan 17, 2011 · Viewed 378.3k times · Source

Is it possible to order when the data is come from many select and union it together? Such as

Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like "%a%"

How can I order this query by name?

I tried this

Select id,name,age
From Student
Where age < 15 or name like "%a%"
Order by name

But that does not work.

Answer

bernd_k picture bernd_k · Jan 17, 2011

Just write

Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like "%a%"
Order by name

the order by is applied to the complete resultset

Related questions

SQL Query - Using Order By in UNION

How can one programmatically sort a union query when pulling data from two tables? For example, SELECT table1.field1 FROM table1 ORDER BY table1.field1 UNION SELECT table2.field1 FROM table2 ORDER BY table2.field1 Throws an exception Note: this …

Read More
Using union and order by clause in mysql

I want to use order by with union in mysql query. I am fetching different types of record based on different criteria from a table based on distance for a search on my site. The first select query returns data …

Read More
Order by clause with Union in Sql Server

I want List of party names with 1st option as 'All' from database. but i won't insert 'All' to Database, needs only retrieve time. so, I wrote this query. Select 0 PartyId, 'All' Name Union select PartyId, Name from PartyMst This …

Read More