How to use Order by in for SQL Query with Union

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

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

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