How To Union 2 table selects with WHEN Cases?

  • 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.

  • CREATE TABLE Table1 (ItemId int)

    CREATE TABLE Table2 (ItemId int)

    INSERT INTO Table1 (ItemId) VALUES ('4');

    INSERT INTO Table1 (ItemId) VALUES ('2');

    INSERT INTO Table1 (ItemId) VALUES ('1');

    INSERT INTO Table2 (ItemId) VALUES ('3');

    INSERT INTO Table2 (ItemId) VALUES ('1');

    INSERT INTO Table2 (ItemId) VALUES ('5');

    INSERT INTO Table2 (ItemId) VALUES ('2');

    SELECT ItemId

    FROM (

    SELECT 1 AS whichTable, *, orderCol = CASE WHEN ItemID = 4 then 1 WHEN ItemID = 2 then 2 WHEN ItemID = 1 then 3 END

    FROM Table1

    WHERE ItemID IN ('4','2','1')

    UNION ALL

    SELECT 2 as whichTable, *, orderCol = 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')

    ) AS data

    ORDER BY whichTable, orderCol;

    Results:

    +--------+

    | ItemId |

    +--------+

    | 4 |

    | 2 |

    | 1 |

    | 3 |

    | 1 |

    | 5 |

    | 2 |

    +--------+

    -- Gianluca Sartori

  • Hi Gianluca! Thanks so much for your reply! I just tried the SELECT in management studio and it returned the results but not with the order of the selected values it orders them after ID rather than the selected order?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply