[sql-server] SQL Server: How to use UNION with two queries that BOTH have a WHERE clause?

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?

This question is related to sql-server union where-clause

The answer is


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.


Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to union

Simplest way to form a union of two lists Group by with union mysql select query How to execute UNION without sorting? (SQL) SQL: how to use UNION and order by a specific select? How can I get the intersection, union, and subset of arrays in Ruby? UNION with WHERE clause SQL Server: How to use UNION with two queries that BOTH have a WHERE clause? Intersection and union of ArrayLists in Java How to order by with union in SQL? SELECT INTO USING UNION QUERY

Examples related to where-clause

Laravel where on relationship object MySQL Select last 7 days SQLSTATE[42S22]: Column not found: 1054 Unknown column - Laravel Conditional WHERE clause in SQL Server Conditional WHERE clause with CASE statement in Oracle How to write a SQL DELETE statement with a SELECT statement in the WHERE clause? MySQL Multiple Where Clause How to use If Statement in Where Clause in SQL? MySQL direct INSERT INTO with WHERE clause MySql Inner Join with WHERE clause