SQL Server Order By in Union with Example

Introduction:

Here I will explain how to use order by in union query in sql server with example or order by clause with union operator in sql server with example or use union operator with order by in sql server or how to combine and use order by and union in sql server with example. To use Order By property with union we need to create sub query for union query statements in sql server.

Description:

In previous articles I explained sql server show exact match values top then partial match values, sql server difference between @@identity, scope_identity and ident_current, joins in sql server with examples, difference between len and datalength functions in sql server, sql server charindex function example, sql server difference between dml, ddl, dcl, tcl, SQL Server restore database from .bak / .mdf file and many articles relating to SQL server. Now I will explain how to use order by clause in union query in sql server 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.

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email