Give priority to union statements

• masoudk1990

  SSCommitted

  Points: 1651

  More actions

  September 15, 2013 at 1:55 am

  #283205

  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.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 15, 2013 at 5:36 am

  #1650270

  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

• masoudk1990

  SSCommitted

  Points: 1651

  More actions

  September 15, 2013 at 6:50 am

  #1650276

  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.