September 15, 2013 at 1:55 am
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.
September 15, 2013 at 5:36 am
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"]
1 A
2 C
1 B
2 B
[/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
September 15, 2013 at 6:50 am
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