Sql-server – SQL Union with results in Order of Union

MySQLsql server

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.

Best Answer

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.

Related Solutions

Sql-server – SQL SERVER – Linked Server and query performance

Your problem begins and ends with statistics and estimates. I have reproduced your situation on my servers and found some interesting hints, and a workaround solution.

First things first, let's take a look at your execution plan:
When a view is used we can see that a filter is applied after the Remote Query is executed, while without the view there was no filter applied at all. The truth is that the filter was applied inside the Remote Query, at the remote server, before retrieving the data over the network.
Well, obviously applying the filter at the remote server and thus retrieving less data is a better option, and obviously that only happens when not using a view.

So... what is so intersting...?

Surprisingly, when I changed the filter from cognome = 'test' to cognome = N'test' (unicode representation of the string) the view used the same execution plan as the first query did.
I guess the reason is that somehow when using the view SQL Server estimated that there will be a small number of rows returning from the (remote) query, and that a local filtering will be cheaper, but when SQL Server had to implicit convert NVARCHAR to VARCHAR, statistics could no longer be used and the decision to filter locally was not taken.
I have looked for the statistics locally, but the view had no statistics, so my guess is that the view uses the remote statistics in a way that ad-hoc query does not, and than takes the wrong decision.

OK, so what solves the problem?

I stated earlier that there is a workaround (at least until someone comes up with a better solution), and no, I don't mean using unicode for your strings.
I wanted to give an answer first, I still have to find why, but when using an Inline Function SQL Server behaves exactly the same as with the query (without view), so replacing the view with the function will give the same result, in a simple query, and with good peformance (at least in my environment).

My code suggestion for you is:

CREATE FUNCTION fn_anagrafiche2()
RETURNS table
AS
RETURN 
(
    SELECT * 
    FROM dolph2.agendasdn.dbo.vistaanagraficagrp
    UNION
    SELECT * 
    FROM dolph2.acampanet.dbo.vistaanagraficagrp
    UNION
    SELECT * 
    FROM municipio2.dbnet.dbo.vistaanagraficagrp
)
GO

The query will then be:

SELECT * 
FROM fn_anagrafiche2()
WHERE cognome = 'prova'

This works on my servers, but of course test it first.
Note: I do not recommend using SELECT * at all, as it is prone to future errors, I simply used it because it was in your question and there was no need for me to change that when I can add this remark instead :)

Mysql – Create multiple columns from a single column filtering criteria

I read the chapter Generating Summaries in MySQL Cookbook, this is where they explain that you can create count, sum and other methods inside your query to gain the proper values in those columns you define, this is known as statistical queries and then this is the query I ended up using:

SELECT user_id as userid, count(id) Total,
COUNT(IF(revision = 'pending', 1, NULL)) as pending,
COUNT(IF(revision = 'approved', 1, NULL)) as approved,
COUNT(IF(revision = 'rejected', 1, NULL)) as rejected
FROM table1
GROUP BY user_id

This will fill those values by count only if criteria is met.

Best Answer

Related Solutions

Sql-server – SQL SERVER – Linked Server and query performance

Mysql – Create multiple columns from a single column filtering criteria

Related Question