How to use Order by in for SQL Query with Union

Posted by Raj.Trivedi under Sql Server on 10/19/2013 | Points: 10 | Views : 1032 | Status : [Member] [MVP] | Replies : 3
Hello Team,

I have a SQL Query where i am using Union and i want to order all the selects in query to order by CreatedDate desc

Here is my Query


SELECT top 1  'ArtView.aspx?id='+Convert(VARCHAR(10),A.ArticleId) AS 'Url',A.ArticleName ,A.ArticleId,A.CategoryId,C.CategoryName
FROM tbl_Article A WITH(NOLOCK)
INNER JOIN tbl_Category C WITH(NOLOCK) ON A.CategoryId = C.CategoryId
WHERE
A.CategoryId = 29
and
A.IsActive = 1 AND A.Language = 1
union
SELECT top 1 'ArtView.aspx?id='+Convert(VARCHAR(10),A.ArticleId) AS 'Url',A.ArticleName ,A.ArticleId,A.CategoryId,C.CategoryName
FROM tbl_Article A WITH(NOLOCK)
INNER JOIN tbl_Category C WITH(NOLOCK) ON A.CategoryId = C.CategoryId
WHERE
A.CategoryId = 33
and
A.IsActive = 1 AND A.Language = 1
union
SELECT top 1 'ArtView.aspx?id='+Convert(VARCHAR(10),A.ArticleId) AS 'Url',A.ArticleName ,A.ArticleId,A.CategoryId,C.CategoryName
FROM tbl_Article A WITH(NOLOCK)
INNER JOIN tbl_Category C WITH(NOLOCK) ON A.CategoryId = C.CategoryId
WHERE
A.CategoryId = 34
and
A.IsActive = 1 AND A.Language = 1


Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved



Responses

Posted by: Bandi on: 10/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
May be this is what you are asking?

SELECT * from (
SELECT 'ArtView.aspx?id='+Convert(VARCHAR(10),A.ArticleId) AS 'Url',A.ArticleName,A.ArticleId,A.CategoryId,C.CategoryName
, row_number() over(partition by a.categoryId Order by createdDate desc) RN
FROM tbl_Article A WITH(NOLOCK) INNER JOIN tbl_Category C WITH(NOLOCK) ON A.CategoryId = C.CategoryId
WHERE A.CategoryId in (33,34,29) and A.IsActive = 1
AND A.Language = 1) tmp
where rn =1



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
I think you wish to get the latest Article details for each category such as 33, 34, and 29...

If this is what your requirement,
you can make use of the following query.. ( No need of UNION and multiple queries)

SELECT Url, ArticleName, ArticleId, CategoryId, CategoryName  
FROM (SELECT 'ArtView.aspx?id='+Convert(VARCHAR(10),A.ArticleId) AS 'Url',A.ArticleName,A.ArticleId,A.CategoryId,C.CategoryName , ROW_NUMBER() OVER(PARTITION BY A.categoryId ORDER BY createdDate desc) RN
FROM tbl_Article A WITH(NOLOCK)
INNER JOIN tbl_Category C WITH(NOLOCK) ON A.CategoryId = C.CategoryId
WHERE A.CategoryId in (33,34,29) and A.IsActive = 1 AND A.Language = 1) tmp
where tmp.RN = 1;


NOTE:
1) ROW_NUMBER() OVER(PARTITION BY A.categoryId ORDER BY createdDate desc) RN
the above OLAP function will give you the latest article details based on CreatedDate in descending order..

2) WHERE A.CategoryId IN (33, 34, 29)
Here i'm using filter for required Categories such as 33, 34, and 29


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Samirbhogayta on: 10/21/2013 [Member] Starter | Points: 25

Up
0
Down
You should be able to alias them and use as subqueries (part of the reason your first effort was invalid was because the first select had two columns (ID and ReceivedDate) but your second only had one (ID) - also, Type is a reserved word in SQL Server, and can't be used as you had it as a column name):I have an example from searching on net.

declare @Tbl1 table(ID int, ReceivedDate datetime, ItemType Varchar(10))
declare @Tbl2 table(ID int, ReceivedDate datetime, ItemType Varchar(10))

insert into @Tbl1 values(1, '20010101', 'Type_1')
insert into @Tbl1 values(2, '20010102', 'Type_1')
insert into @Tbl1 values(3, '20010103', 'Type_3')

insert into @Tbl2 values(10, '20010101', 'Type_2')
insert into @Tbl2 values(20, '20010102', 'Type_3')
insert into @Tbl2 values(30, '20010103', 'Type_2')

SELECT a.ID, a.ReceivedDate FROM
(select top 2 t1.ID, t1.ReceivedDate
from @tbl1 t1
where t1.ItemType = 'TYPE_1'
order by ReceivedDate desc
) a
union
SELECT b.ID, b.ReceivedDate FROM
(select top 2 t2.ID, t2.ReceivedDate
from @tbl2 t2
where t2.ItemType = 'TYPE_2'
order by t2.ReceivedDate desc
) b


SAMIR
Sr. Software Engineer

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response