CODEWITHSUNDEEP
sqlsql-serversql-server-2008
I A Khan
I A Khan

Reputation: 8839

Union All in SQL server with Order By For All Table

I have two table TABLE_A and TABLE_B, and I am using Union All to get records with Order By with the help of following in SQL server 2008

SELECT  * 
FROM 
 (
   SELECT *, 1 sortby FROM TABLE_A 
   UNION ALL 
   SELECT *, 2 sortby FROM TABLE_B
  ) Temp
ORDER   BY sortby

but the problem is above statement is Order By only data from Table_A, and Table_B is not with Order By, I want to add Order By condition on both tables, data should be come Assending from TABLE_A first then after Assending from TABLE_B.

How to do this.

Upvotes: 0

Views: 91

Answers (2)

arunb2w
arunb2w

Reputation: 1196

We can also do like this

SELECT  * 
FROM 
 (
   SELECT *, 1 firstsortby, 2 secondsortby FROM TABLE_A 
   UNION ALL 
   SELECT *, 2 firstsortby, 1 secondsortby FROM TABLE_B
  ) Temp
ORDER BY firstsortby, secondsortby

Upvotes: 1

juergen d
juergen d

Reputation: 204766

Add the other column you want to sort by in the order clause

SELECT  * 
FROM 
 (
   SELECT *, 1 sortby FROM TABLE_A 
   UNION ALL 
   SELECT *, 2 sortby FROM TABLE_B
  ) Temp
ORDER BY sortby, other_column_of_result

Upvotes: 8

Related Questions