Override alphabetical default ORDER BY with a UNION of 2+ tables?

Really quick question... I have 4 tables that are UNION-ed together like so:

SELECT * FROM table1  
UNION
SELECT * FROM table2  
UNION
SELECT * FROM table3  
UNION
SELECT * FROM table4

Without specifying an ORDER BY, the query orders by the first column in ascending alphabetical o开发者_JAVA百科rder (which in my case happens to be a varchar type). I don't want ORDER BY [Column1] DESC either.

I simply want to order the results in the same order as the tables themselves are UNION-ed. 1, 2, 3, 4.

Is there a simply way to do this?

Thanks!!

One way

SELECT *,1 as SortOrder FROM table1  
UNION
SELECT *,2 FROM table2  
UNION
SELECT *,3 FROM table3  
UNION
SELECT *,4 FROM table4
order by SortOrder 

what happens is that you are using UNION, sql server then makes the result set distinct, in order to do that it needs to sort the tables

Does UNION ALL make a difference?

We had a similar issue. We have a union query with 32 subsets. We use it to populate a spreadsheet that is then used to build a PowerPoint presentation. The first field in each query is a text field that is a description of the data point. The spreadsheet is expecting the data to be in a specific order.

We made a slight change to one of the fields:

Concat('Annual incidence rate- ', Year(start_date))

This caused SQL to sort the unions in alphabetical order! I suspect that if you ordered your query as @SQLMenace indicated, but with a slight modification, it would work too.

SELECT '1', * FROM table1  
UNION
SELECT '2', * FROM table2  
UNION
SELECT '3', * FROM table3  
UNION
SELECT '4', * FROM table4

This might alleviate the outer query wrapper. It might work without the quotes, too.