UNION ALL on 2 Selects With WHEN Cases

Transact-SQL
1

Hi guys, I'm really looking for help. I'm not sure if T-SQL is the right category?

In short, I'm running MS SQL 2005. I have 2 tables.

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 as they were selected.

ie.

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

Extremely grateful for any and all help.

It's my first post...so apologies if I have posted in the wrong section.

Scott

2

Maybe this:

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

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