SQL Server Order By in Union with Example

Generally in sql server it’s not possible to use order by clause directly with union statements. To use order by clause in sql server with union statement we need to create sub query for union statements then only we can apply order by clause in sql server.

Following is the sample sql server query to use order by clause with union statements.

SELECT * FROM ( Select Id as UserId, UserName as Name, RegisterDate From UserDetails Union select UserId, EmpName as Name, ModifyDate as RegisterDate From UserDetails ) smptbl ORDER BY RegisterDate DESC

If you observe above query we are getting data from union statements as sub query and applying order by statement sub query to show the data in descending order.

Now we will see how to use order by clause with union statements in sql server with example.

DECLARE @temp1 table(id int, name varchar(50),modifydate date) DECLARE @temp2 table(id int, name varchar(50),modifydate date) insert into @temp1(id,name,modifydate) values(1,'Suresh,Dasari','2016-01-30'), (2,'Rohini,Alavala','2016-02-10'), (3,'Madhav,Sai','2016-03-05') insert into @temp2(id,name,modifydate) values(13,'Honey','2016-04-15'), (21,'Praveen,Alavala','2016-02-05'), (21,'Sateesh,Chandra','2016-01-20') SELECT * FROM ( Select id,name, modifydate from @temp1 UNION Select id,name, modifydate from @temp2 ) stbl ORDER BY modifydate DESC

If we execute above query we will get records in modifydate descending order.

Output

Following is the result of using sql server order by clause with union query.