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 ID
s 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?
This question is related to
sql-server
union
where-clause
select * from
(
select top 2 t1.ID, t1.ReceivedDate
from Table t1
where t1.Type = 'TYPE_1'
order by t1.ReceivedDate de
) t1
union
select * from
(
select top 2 t2.ID
from Table t2
where t2.Type = 'TYPE_2'
order by t2.ReceivedDate desc
) t2
or using CTE (SQL Server 2005+)
;with One as
(
select top 2 t1.ID, t1.ReceivedDate
from Table t1
where t1.Type = 'TYPE_1'
order by t1.ReceivedDate de
)
,Two as
(
select top 2 t2.ID
from Table t2
where t2.Type = 'TYPE_2'
order by t2.ReceivedDate desc
)
select * from One
union
select * from Two
The basic premise of the question and the answers are wrong. Every Select in a union can have a where clause. It's the ORDER BY in the first query that's giving yo the error.
The answer is misleading because it attempts to fix a problem that is not a problem. You actually CAN have a WHERE CLAUSE in each segment of a UNION. You cannot have an ORDER BY except in the last segment. Therefore, this should work...
select top 2 t1.ID, t1.ReceivedDate
from Table t1
where t1.Type = 'TYPE_1'
-----remove this-- order by ReceivedDate desc
union
select top 2 t2.ID, t2.ReceivedDate --- add second column
from Table t2
where t2.Type = 'TYPE_2'
order by ReceivedDate desc
Create views on two first "selects" and "union" them.
declare @T1 table(ID int, ReceivedDate datetime, [type] varchar(10))
declare @T2 table(ID int, ReceivedDate datetime, [type] varchar(10))
insert into @T1 values(1, '20010101', '1')
insert into @T1 values(2, '20010102', '1')
insert into @T1 values(3, '20010103', '1')
insert into @T2 values(10, '20010101', '2')
insert into @T2 values(20, '20010102', '2')
insert into @T2 values(30, '20010103', '2')
;with cte1 as
(
select *,
row_number() over(order by ReceivedDate desc) as rn
from @T1
where [type] = '1'
),
cte2 as
(
select *,
row_number() over(order by ReceivedDate desc) as rn
from @T2
where [type] = '2'
)
select *
from cte1
where rn <= 2
union all
select *
from cte2
where rn <= 2
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order. you are selecting
t1.ID, t2.ReceivedDate from Table t1
union
t2.ID from Table t2
which is incorrect.
so you have to write
t1.ID, t1.ReceivedDate from Table t1 union t2.ID, t2.ReceivedDate from Table t1
you can use sub query here
SELECT tbl1.ID, tbl1.ReceivedDate FROM
(select top 2 t1.ID, t1.ReceivedDate
from tbl1 t1
where t1.ItemType = 'TYPE_1'
order by ReceivedDate desc
) tbl1
union
SELECT tbl2.ID, tbl2.ReceivedDate FROM
(select top 2 t2.ID, t2.ReceivedDate
from tbl2 t2
where t2.ItemType = 'TYPE_2'
order by t2.ReceivedDate desc
) tbl2
so it will return only distinct values by default from both table.
Source: Stackoverflow.com