SQL Union with results in Order of Union

Question

I'm looking to write a Union between 2 tables where the 1st Union set of results appears before the second. (MSSQL 2014)

http://www.sqlfiddle.com/#!6/17c9e/74

I've noticed this question asked a few times but for whatever reason the accepted answers aren't working for me:

https://stackoverflow.com/questions/4076052/union-query-display-results-in-order-queries-are-written

https://stackoverflow.com/questions/13885261/order-result-sets-in-a-union

The 1st set of results returns:

userID   FirstName   LastName
-----------------------------
4        John        Snow
5        Joffrey     Baratheon
1        Tyrion      Lanister

And the 2nd results return:

userID   FirstName   LastName
-----------------------------
5        Joffrey     Baratheon
3        Daenerys    Targaryen
1        Tyrion      Lanister

Union gets rid of the duplicates which leaves userID 3 which should be appearing very last in my results. Final result:

userID   FirstName   LastName
-----------------------------
4        John        Snow
5        Joffrey     Baratheon
1        Tyrion      Lanister
3        Daenerys    Targaryen

The result I'm getting on SqlFiddle has userID -3 appearing second.

Any help appreciated.

Answer 1

Based on your sqlfiddle link, here's a brute force way to get what you're looking for. I inject an ordering value for each result set being unioned, group on matching values to get the minimum order, and then order by the minimum order for each grouped value.

SELECT userID, FirstName, LastName, UserName
FROM (
  SELECT userID, FirstName, LastName, UserName, MIN(UnionSet) UnionSetOrder
  FROM (
    SELECT u.userID, u.FirstName, u.LastName, u.Username, 1 as UnionSet
    FROM follows f INNER JOIN Users u ON f.Following = u.UserID
    WHERE follower=2
    UNION ALL
    SELECT u.UserId, u.FirstName, u.LastName, U.Username, 2 as UnionSet
    FROM users u 
    WHERE u.username LIKE '%a%' AND u.UserID <> 2
  ) x
GROUP BY userID, FirstName, LastName, UserName  
) y
ORDER BY UnionSetOrder, UserName

Note that this doesn't exactly match the results you're showing in your question, as they differ from what's in the code sample.

Answer 2

To put it bluntly: If you want a particular ordering of the rows, you must ask for it.

A pattern for UNION is

( SELECT ... )
UNION    -- tack on ALL or DISTINCT according to your need
( SELECT ... )
ORDER BY ...

There is no need for ORDER BY in the inner queries unless you want to use LIMIT and/or OFFSET.

If you need to force the "first" SELECT to come first (and don't have a column that happens to provide such ordering), then you may need an extra column:

( SELECT 1 AS seq, ... )
UNION    -- tack on ALL or DISTINCT according to your need
( SELECT 2 AS seq, ... )
ORDER BY seq, ...

If you really don't want seq cluttering` the output, then:

SELECT ...    -- without `seq`
    FROM (
        ( SELECT 1 AS seq, ... )
        UNION    -- tack on ALL or DISTINCT according to your need
        ( SELECT 2 AS seq, ... )
         ) AS u
    ORDER BY seq, ...

If you have some messy JOINs, it may be better to move some of them to the outer SELECT.

Answer 3

In MySQL, its showing below result:

mysql> select * from set1 UNION select * from set2;
+---------+--------------+--------------+
| userID  | FirstName    | Lastname     |
+---------+--------------+--------------+
|       4 | John         | Snow         |
|       5 | Joffrey      | Baratheon    |
|       1 | Tyrion       | Lanister     |
|       3 | Daenerys     | Targaryen    |
+---------+--------------+--------------+