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.

If both tables have the same columns and you are using `UNION ALL` to get data from both they are clearly realated. So you should use one table with another column `TypeID`. — Tim Schmelter, Jun 17 '15 at 08:39

shA.t · Answer 1 · 2015-06-17T09:55:07.100

3

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

edited Jun 17 '15 at 09:55

answered Jun 17 '15 at 08:28

shA.t

16,580
5
54
111

Thanks for the reply shA.t ...really appreciate it. I tried the select but it doesn't maintain the IN order of (4,2,1) and (3,1,5,2). It does the union but results as (1,2,4,1,2,3,5) in other words it just Orders them by ID. Any ideas? – Steve Jun 17 '15 at 08:57
The inner `CASE` expressions are the wrong way around. – Damien_The_Unbeliever Jun 17 '15 at 09:15
Damien, nice spot! Eyes of an eagle!!!! I just saw it. Giorgos post solved the problem. Thanks again guys. This is such a great forum! – Steve Jun 17 '15 at 09:43
@Damien_The_Unbeliever Thanks, I edit the answer ;). – shA.t Jun 17 '15 at 09:55
@Steve I also remove `'` characters inside `IN` statement ;). – shA.t Jun 17 '15 at 09:56

Giorgos Betsos · Accepted Answer · 2015-06-17T09:19:45.680

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

Hey Giorgos, thanks for the reply here. Really appreciate the time you take to help. Message I get from MS SQL 2005 is CROSS APPLY is not supported? — Steve, Jun 17 '15 at 09:00
@Steve `CROSS APPLY` was introduced with SQL Server 2005. There was an error in my query. It run OK now. If you don't want to use `CROSS APPLY` you can use an alternative syntax (chek edit). — Giorgos Betsos, Jun 17 '15 at 09:16
I've ticked it as the answer to the question but it won't let me vote up? I need a reputation of 15 and sadly I'm a newbie at this with just 1...but I'd really like to add credit to you here. Amazing job!!!! — Steve, Jun 17 '15 at 09:30

UNION ALL on 2 tables select with Cases

2 Answers2