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

User · Answer

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.

User · Answer

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  lt   2 union all select   from cte2 where rn  lt   2

User · Answer

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

User · Answer

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

User · Answer

Create views on two first  selects  and  union  them

User · Answer

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

User · Answer

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

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

Examples related to sql-server

Examples related to union

Examples related to where-clause