Issue

This Content is from Stack Overflow. Question asked by Mohammad

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]

Solution

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

This Question was asked in StackOverflow by Mohammad and Answered by Dale K It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.