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?

I guess you mean that you have trouble with the `order by` not `where`. — Mikael Eriksson, Mar 24 '11 at 23:39

Ken White · Accepted Answer · 2011-03-24T23:45:27.963

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

score 10 · Answer 2 · answered Mar 24 '11 at 23:40

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

This is the best answer, especially using the CTE. Just on a point of style, Microsoft recommend ending _all_ SQL statements with a semicolon, and will require it in a future version. If you end all of your statements with a semicolon, you never need to fake it by preceding the `WITH` with one. — Manngo, Nov 17 '21 at 08:14

score 6 · Answer 3 · answered Mar 24 '11 at 23:35

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

score 4 · Answer 4 · answered Aug 17 '13 at 04:13

4

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.

answered Aug 17 '13 at 04:13

Paul Nielsen

41
1

So my comment = 15 characters, but THIS is the answer. – Hatt Mar 07 '14 at 17:04

score 3 · Answer 5 · answered Jun 09 '15 at 15:34

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

The `TOP 2` in the first query requires the first `ORDER BY` — OGHaza, Sep 16 '16 at 01:53

score 0 · Answer 6 · answered May 30 '14 at 05:25

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.

score 0 · Answer 7 · answered Mar 24 '11 at 23:21

0

Create views on two first "selects" and "union" them.

answered Mar 24 '11 at 23:21

Pavel Nefyodov

876
2
11
29

3

-1 Creating two views just in order to select and union them seems wrong. – Ken White Mar 24 '11 at 23:37
+1 because this answer is technically correct, but its not the best solution. – aarona Mar 24 '11 at 23:45
@Ken White Do you really think it's a good idea to vote down answers just because they are not the best solution? It's technically correct solution given in 30 seconds and with no code involved. @DJTripleThreat: Thank you for your professional attitude. – Pavel Nefyodov Mar 25 '11 at 09:24
2

Yes, I do. The whole point of this site is to find the best answers to questions, and to provide the best answers you can to other's questions. The reputation system rewards people for giving correct (and well thought out) answers, and people who give quick, poorly conceived or wrong answers lose reputation. "Technically correct" isn't always appropriate (or right). I could say that the solution was to print out each querie's result and put them together with scissors and paste - while "technically correct", because it would work, would you vote for that answer? – Ken White Mar 25 '11 at 11:30
Its my opinion that if the answer has the desired outcome, it shouldn't be downvoted. However, not every correct answer should be upvoted if its not useful. Pavel was also the first to respond so I usually upvote any correct answers that I get within the first hour I post. Thats just me though. – aarona Mar 25 '11 at 18:31

score 0 · Answer 8 · answered Jun 08 '21 at 20:31

0

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

answered Jun 08 '21 at 20:31

Valentin Belanovskiy

1

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

8 Answers8

Linked