CODEWITHSUNDEEP
sql-server
Maryam Khan
Maryam Khan

Reputation: 3

Order by in union clause

Could any one please help me I have been working on a query containing unions n joins of multiple tables.. I have got the desired results but I want to get these results in some specific order so the whole result is being orderd according to one column. Here is the snippet of code I am working on:

select name, age
from UserUni
order by age
union all
select age, Name
from UserOffice
order by age

Upvotes: 0

Views: 44

Answers (2)

UsmanMirza
UsmanMirza

Reputation: 276

when we are using we can not use order by with both statements. because union at the end give one result end so how is it possible to use two order by statements. you can check details here.

https://finalcodingtutorials.blogspot.ae/2017/03/order-by-clause-with-union-in-sql-server.html

hopefully it will resolve your issue will let you know complete details or union and order by statement.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522751

Just add an ORDER BY clause at the very end of the UNION query, and it should be applied to the entire query:

select name, age
from UserUni
union all
select name, age
from UserOffice
order by age

Note that I swapped the order of the columns appearing in the second half of the UNION query because it doesn't make sense to put age and name into the same column. It is generally a requirement in a UNION query that the types and number of all columns be the same in boths halves of the query. One exception might be MySQL, which might appear to allow mixing numbers and text, but even in this case some implicit type conversion would be happening underneath the hood.

Upvotes: 1

Related Questions