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