SQL Server: How to use UNION with two queries that BOTH have a WHERE clause?

Question

Given:

Two queries that require filtering:

select top 2 t1.ID, t1.ReceivedDate   from Table t1  where t1.Type = 'TYPE_1'  order by t1.ReceivedDate desc 

And:

select top 2 t2.ID   from Table t2  where t2.Type = 'TYPE_2'  order by t2.ReceivedDate desc 

Separately, these return the IDs I'm looking for: (13, 11 and 12, 6)

Basically, I want the two most recent records for two specific types of data.

I want to union these two queries together like so:

select top 2 t1.ID, t2.ReceivedDate   from Table t1  where t1.Type = 'TYPE_1'  order by ReceivedDate desc union select top 2 t2.ID   from Table t2  where t2.Type = 'TYPE_2'  order by ReceivedDate desc 

Problem:

The problem is that this query is invalid because the first select cannot have an order by clause if it is being unioned. And it cannot have top 2 without having order by.

How can I fix this situation?

Answer 1

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):

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