1

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.

  • It would be helpful if you included a table of what you expect to see as a result, just to be sure. Also, your result tables above don't match the query in your sqlfiddle sample. – mathewb Nov 1 '17 at 18:45
  • The final result will look as it appears on the in sqlfiddle the only difference is userID 3 - Daenerys should be very last. The two results shown are whats returned by the individual queries before the union. – sacbemeezer Nov 1 '17 at 18:56
  • Sorry, I meant the Username field, specifically. It's missing from these results, so it's unclear why the results should be ordered in that manner. Including it here helps ensure that the question itself is complete, even without the code sample at sqlfiddle. – mathewb Nov 1 '17 at 20:00
  • choose any one of (MySQL & SQL Server) which is different DB Products. – Yogesh Sharma Nov 2 '17 at 6:16
  • Your question appears to be based on the (false) premise that each of the individual SELECTs is supposed to always return the results in the same order, even though there's no obvious sorting criterion in your examples, nor does your demo suggest any. If you want to have a solution to the problem as presented, you first need to address that issue, i.e. you need to provide a sorting criterion that can be used to guarantee the 4, 5, 1 order for the first set and 5, 3, 1 for the second. It's pointless to try to meet your requirements without that, so I'm voting to put this on hold as unclear. – Andriy M Nov 2 '17 at 13:24
2

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.

1

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.

  • Can you attempt this in the SqlFiddle? sqlfiddle.com/#!6/17c9e/74 if the link doesn't work try a refresh of the page. – sacbemeezer Nov 2 '17 at 15:38
  • By adding the seq field, won't the union operator consider matching records from the first and second sets as different? – mathewb Nov 2 '17 at 16:08
  • (my opinion) The implementation of UNION is simple-minded. In fact (until very recently), it blindly created a tmp table, regardless of what the statement was doing.) – Rick James Nov 3 '17 at 21:30
0

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    |
+---------+--------------+--------------+
  • Question is whether it's guaranteed to always work like that, though. – Andriy M Nov 2 '17 at 11:19
  • That isn't guaranteed: with no explicit ordering stated the query planner and engine are at liberty to pull things out in any order and may do something different as the data changes/grows or in later versions of the engine. I'm surprised you see this order: UNION without ALL implies DISTINCT which in turn is usually implemented by a sort by a primary-or-otherwise-unique key if one exists (I'm assuming userID is the PK here). – David Spillett Nov 2 '17 at 11:39
  • Were you unable to try this from SqlFiddle? If it doesn't immediately show try a refresh of the page. – sacbemeezer Nov 2 '17 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.