Give priority to union statements

  • Hi, if I have several SELECT statements with UNION how can I tell to sql to process which one first?

    Please take a look at this structure:

    CREATE TABLE #table1 (ID INT, Title1 NVARCHAR(1), Title2 NVARCHAR(1));

    INSERT INTO #table1 VALUES(1,'A','B')

    INSERT INTO #table1 VALUES(2,'C','B')

    Now I wish to INSERT the values from #table1 to my CTE with this query:

    WITH CTE (ID,Title) AS (

    SELECT ID, Title1 FROM #Table1 --I expect this query run first

    UNION ALL

    SELECT ID, Title2 FROM #Table1 --I expect this query run after above query

    )

    SELECT * FROM CTE

    But result is something different CTE sort my table automatically and give me this output:

    ID,Title

    ---------

    1,'A'

    1,'B'

    2,'B'

    2,'C'

    But my query should have this output:

    ID,Title

    ---------

    1,'A'

    1,'B'

    2,'C'

    2,'B'

    How can I generate the output I want?

    Thank you for help

    ___________________________________
    Computer Enterprise Masoud Keshavarz
    I don't care about hell.
    If I go there I've played enough Diablo to know how to fight my way out.

  • If you need a particular order by on a set of rows, then you need an order by statement on the outer-most select. This isn't about order of subqueries executed, you're assuming you can get a required order without an order by. This is not the case.

    No order by, no guarantee of order. End of story.

    When I run your code, I get the rows ordered as :

    [font="Courier New"]

    1A

    2C

    1B

    2B

    [/font]

    To get the order you want, try this:

    WITH CTE (ID,Title, Ordering) AS (

    SELECT ID, Title1, 1 AS Ordering FROM #Table1

    UNION ALL

    SELECT ID, Title2, 2 AS Ordering FROM #Table1

    )

    SELECT ID, Title FROM CTE

    ORDER BY ID, Ordering

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Much appreciated Gail Shaw.

    Thank you very much.

    ___________________________________
    Computer Enterprise Masoud Keshavarz
    I don't care about hell.
    If I go there I've played enough Diablo to know how to fight my way out.

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

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