Sql server union but keep order

Question

Is there a way to union two tables, but keep the rows from the first table appearing first in the result set?

For example:

Table1

name        surname ------------------- John         Doe Bob          Marley Ras          Tafari 

Table2

name       surname ------------------ Lucky      Dube Abby       Arnold 

I want the result set to be:

name        surname ------------------- John         Doe Bob          Marley  Ras          Tafari Lucky      Dube Abby       Arnold 

Unfortunately, union somehow reorders the table. Is there a way around this?

Answer 1

Try this :-

Select *  from (  Select name,surname, 1 as filter from  Table1 Union all Select name,surname , 2 as filter from Table2 ) order by filter 

Answer 2

The only way to guarantee output order is to use ORDER BY:

SELECT name,surname,1 as rs FROM table1 UNION ALL SELECT name,surname,2 FROM table2 ORDER BY rs 

If you don't want rs to appear in the final result set, do the UNION as a subquery:

SELECT name,surname FROM (   SELECT name,surname,1 as rs   FROM table1   UNION ALL   SELECT name,surname,2   FROM table2 ) t ORDER BY rs