Menu
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?
296
UNIONS cause orders to be lost as it does a match up. You can fake it by adding a bogus limit to the union, then order the whole result.
Within UNION each SELECT statement must have the same columns number. The columns must have analogous data types. In each SELECT statement, the columns must be in the same order.
You can use UNION ALL to avoid sorting, but UNION ALL will return duplicates. So you only use UNION ALL to avoid sorting if you know that there are no duplicate rows in the tables).
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 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
