How to use order by with Union in SQL

No.of Views	:	1988
Bookmarked	:	0 times
Downloads	:
Votes	:	0

By RRaveen On 24 Sep 2010 10:09:37

Tag : Sql Servers , T-SQL

Skill Level: Intermediate

In this code snippet, you will learn how to use the order by with Union or Union All

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

Introduction

In this code snippet, I will demonstration to you all how to use the Order by clause with Union or Union all

Union

select a.* from (select top 10 ToUrl, Title+ ' (' +ltrim(rtrim(cast(HitCount as CHAR(6)))) +' views)' as Title,Description,ContentTypeIDfrom contentswhere IsActive=1 and IsPublsihed=1 and ContentTypeID=1order by HitCount desc ) as aUNIONselect b.* from (select top 10 ToUrl,Title+ ' (' +ltrim(rtrim(cast(HitCount as CHAR(6)))) +' views)' as Title,Description,ContentTypeIDfrom contentswhere IsActive=1 and IsPublsihed=1 and ContentTypeID=2order by HitCount desc) bUNIONselect c.* from (select top 10 UrlSlug as ToUrl,Title+ ' (' +ltrim(rtrim(cast(ViewCount as CHAR(6)))) +' views)' as Title,Description,ContentTypeIDfrom TipsAndTrickswhere IsActive=1 and IsPublished=1 and ContentTypeID=9order by ViewCount desc) c

In above sql, you can see have I have used the sub query to order the record first, and then select from that ordered data to another table and populate data from that table.

Union All

select a.* from (select top 10 ToUrl, Title+ ' (' +ltrim(rtrim(cast(HitCount as CHAR(6)))) +' views)' as Title,Description,ContentTypeIDfrom contentswhere IsActive=1 and IsPublsihed=1 and ContentTypeID=1order by HitCount desc ) as aUNIONALLselect b.* from (select top 10 ToUrl,Title+ ' (' +ltrim(rtrim(cast(HitCount as CHAR(6)))) +' views)' as Title,Description,ContentTypeIDfrom contentswhere IsActive=1 and IsPublsihed=1 and ContentTypeID=2order by HitCount desc) bUNION ALLselect c.* from (select top 10 UrlSlug as ToUrl,Title+ ' (' +ltrim(rtrim(cast(ViewCount as CHAR(6)))) +' views)' as Title,Description,ContentTypeIDfrom TipsAndTrickswhere IsActive=1 and IsPublished=1 and ContentTypeID=9order by ViewCount desc) c

I have used same idea to union all as well. hope help to you all. thank you for reading.

About Author

RRaveen

Occupation	-	Software Engineer
Company	-	TGS
Member Type	-	Gold
Location	-	Singapore
Joined date	-	03 Jun 2009
Home Page	-	codegain.com
Blog Page	-	www.codegain.com

- B.Sc. degree in Computer Science. - 4+ years experience in Visual C#.net and VB.net - Obsessed in OOP style design and programming. - Designing and developing Network security tools. - Designing and developing a client/server application for sharing files among users in a way other than FTP protocol. - Designing and implementing GSM gateway applications and bulk messaging. - Windows Mobile and Symbian Programming - Having knowledge with ERP solutions


Other popularSectionarticles
SQL Query to sort stored procedure on Modified date or Created Date SQL Query to sort stored procedure on Modified date or Created Date Published Date : 17/May/2010 Resetting Identity Column in SQL Server How to reset Identity Column in SQL Server Published Date : 06/Apr/2010 SQL Server 2008 Installation Step By Steps SQL Server 2008 Installation Step By Steps Published Date : 15/Feb/2010 How to update view when new column added in table in sql server In this snippet, help to learn,How to update view when new column added in table in sql server Published Date : 13/Oct/2010 How to reset Auto Increment counter in MS SQL Server Today, I am working an enterprise systems, suddenly, we have got a situation that we need to reset most of the tables have AUTO INCREMENT columns. Hence, I write a simple script it do. Published Date : 07/Feb/2013

Comments

There is no comments for this articles.