How to use order by with Union in SQL

No.of Views2575
Bookmarked0 times
Downloads 
Votes0
By  RRaveen   On  24 Sep 2010 10:09:37
Tag : Sql Servers , T-SQL
In this code snippet, you will learn how to use the order by with Union or Union All
emailbookmark add commentsprint

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.

 
Sign Up to vote for this article
 
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
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top