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

Create views on two first  selects  and  union  them

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

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

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

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

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

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