Show each table after the other in UNION SQL-CodePudding

I want to show two result sets in one result set, using UNION

The problem is, I want it to show all of the first table's records, and then go to the next one

But since SQL Server automatically orders the results by the first column, the final results get ordered by ID column, which both tables have

Now, there is a record in [table 1] with the ID value of "1", and there is also a record in [table 2] with the ID value of "1"

Same goes for ID value being 2, 3, 4 ,5 ...

Now, SQL Server orders all the final records by ID, and therefore, it is shown like this:

[table 1].[record 1]
[table 2].[record 1]
[table 1].[record 2]
[table 2].[record 2]

I want it to be like this:

[table 1].[record 1]
[table 1].[record 2]
[table 1].[record 3]

and so on, until all the [table 1]'s records are displayed, and then go to [table 2]'s records

[table 2].[record 1]
[table 2].[record 2]
[table 2].[record 3]

CodePudding user response：

Add an "ordering column" e.g.

select 0 Orderb, T1.*
from Table1 T1

union all

select 1 Orderb, T2.*
from Table2 T2

order by OrderBy, id; -- whatever columns you wish to order by