UNION ALL on 2 tables select with Cases

Question

I'm running SQL Server 2005. I have 2 tables with the same columns but holding very different data.

SELECT *
FROM Table1 
WHERE ItemID IN ('4','2','1') 
ORDER BY 
    CASE  WHEN ItemID = 4 then 1 WHEN ItemID = 2 then 2 WHEN ItemID = 1 then 3 END 
UNION ALL 
SELECT * 
FROM Table2 
WHERE ItemID IN ('3','1','5','2') 
ORDER BY 
    CASE  WHEN ItemID = 3 then 4 WHEN ItemID = 1 then 5 WHEN ItemID = 5 then 6 WHEN ItemID = 2 then 7 END

I need to keep the order of the ItemID in the order that they are selected which is why I used CASE. This all works fine on each table but I can't find a way to combine them into 1 table of results with the results of each table ordered.

i.e.

4 (Table1)
2 (Table1)
1 (Table1)
3 (Table2)
1 (Table2)
5 (Table2)
2 (Table2)

Extremely grateful for any and all help.

Answer 1

You can use the following query:

SELECT Table1.* , x.[Order] AS Ord
FROM Table1 
CROSS APPLY (SELECT CASE ItemID
                       WHEN 4 THEN 1 
                       WHEN 2 THEN 2 
                       WHEN 1 THEN 3 
             END) x([Order])  
WHERE ItemID IN ('4','2','1') 

UNION ALL 

SELECT Table2.* , y.[Order] AS Ord
FROM Table2 
CROSS APPLY (SELECT CASE ItemID 
                       WHEN 3 THEN 4 
                       WHEN 1 THEN 5 
                       WHEN 5 THEN 6 
                       WHEN 2 THEN 7 
                    END) y([Order])
WHERE ItemID IN ('3','1','5','2')
ORDER BY Ord

Calculated field [Order] guarantees that records from Table1 will come first, followed by records of Table2. It also ensures ordering within each Table1 or Table2 partition.

Demo here

This is an alternative syntax without the use of CROSS APPLY:

SELECT Table1.*, 
       CASE ItemID
          WHEN 4 THEN 1 
          WHEN 2 THEN 2 
          WHEN 1 THEN 3 
       END AS Ord
FROM Table1 
WHERE ItemID IN ('4','2','1') 

UNION ALL 

SELECT Table2.*, 
       CASE ItemID 
          WHEN 3 THEN 4 
          WHEN 1 THEN 5 
          WHEN 5 THEN 6 
          WHEN 2 THEN 7 
       END AS Ord
FROM Table2 
WHERE ItemID IN ('3','1','5','2')
ORDER BY Ord

Demo here

Answer 2

Try this:

SELECT *
FROM (
    SELECT * , 1 as ord
    FROM Table1 
    WHERE ItemID IN (4, 2, 1) 
  UNION ALL 
    SELECT * , 2 as ord
    FROM Table2 
    WHERE ItemID IN (3, 1, 5, 2) ) t
ORDER BY  
    ord,
    CASE  WHEN ItemID = 3 then 4 WHEN ItemID = 1 then 5 WHEN ItemID = 5 then 6 WHEN ItemID = 2 then 7 END,
    CASE  WHEN ItemID = 4 then 1 WHEN ItemID = 2 then 2 WHEN ItemID = 1 then 3 END 

---

Or

SELECT *,
    ROW_NUMBER() OVER (ORDER BY ord,
        CASE  WHEN ItemID = 3 then 4 WHEN ItemID = 1 then 5 WHEN ItemID = 5 then 6 WHEN ItemID = 2 then 7 END,
        CASE  WHEN ItemID = 4 then 1 WHEN ItemID = 2 then 2 WHEN ItemID = 1 then 3 END) as RowNo 

FROM (
    SELECT * , 1 as ord
    FROM Table1 
    WHERE ItemID IN (4, 2, 1) 
  UNION ALL 
    SELECT * , 2 as ord
    FROM Table2 
    WHERE ItemID IN (3, 1, 5, 2) ) t