tsql union, order by, and view

Question

I am trying to pull a set of data from tables in different databases (on the same server (2014) and identical table structure) and have it in a view. I've found several discussions about set ordering and unions, but I having some problems figuring out the result set itself. Here is the script:

CREATE VIEW OrderCheck (Source, [Order No], [Date], FirstName) as

SELECT Top 2 'Set1', Order_No, format(Order_Date,'g'), First_Name
FROM        db1.dbo.Orders 
Union all
SELECT Top 2 'Set2', Order_No, format(Order_Date,'g'), First_Name
FROM        db2.dbo.Orders
Union all
SELECT Top 2 'Set3', Order_No, format(Order_Date,'g'), First_Name 
FROM        db3.dbo.Orders

ORDER BY    Order_No desc

If I run the select/union query by itself, I get these results, which is what I want:

Set3    60098   9/14/2016 8:32 AM   Lydia
Set3    60097   9/13/2016 12:28 PM  Jeané
Set1    30098   9/19/2016 4:12 PM   Joshua
Set1    30097   9/19/2016 1:35 PM   Cynthia
Set2    11050   9/20/2016 9:24 AM   Lorrie
Set2    11049   9/19/2016 11:12 PM  Dawnisha

But when I use the view (select * from OrderCheck), I get this:

Set1    10011   11/14/2013 9:13 AM  Jaime
Set1    10012   11/14/2013 9:54 AM  Eric
Set2    10011   11/14/2013 9:13 AM  Jaime
Set2    10012   11/14/2013 9:54 AM  Eric
Set3    60098   9/14/2016 8:32 AM   Lydia
Set3    60097   9/13/2016 12:28 PM  Jeané

Finally, if I run the query inside or outside of a view but without any "order by", I get this:

Set1    10011   11/14/2013 9:13 AM  Jaime
Set1    10012   11/14/2013 9:54 AM  Eric
Set2    10011   11/14/2013 9:13 AM  Jaime
Set2    10012   11/14/2013 9:54 AM  Eric
Set3    10011   11/14/2013 9:13 AM  Jaime
Set3    10012   11/14/2013 9:54 AM  Eric

So you can see that in the view, set 1 rows replace set 2 rows except for the string literal. And without the "order by", set 1 replaces both set 2 and set 3. "Union All" vs "Union" makes no difference, nor does the number of TOP rows requested. Seems bizarre to me, but I'm hoping there is a logical explanation that I'm just missing despite my research.

Thank you so much! Jim Kelleher

Answer 1

Order by is not guaranteed unless you use an outer order by

select * from view
order by order_no desc/asc  

guarantees order

Below is a Quote from Itzik Ben-Gan

The ORDER BY clause in the view(when using Top*) is only guaranteed to serve the logical filtering purpose for the TOP option. If you query the view and don’t specify an ORDER BY clause in the outer query, presentation order is not guaranteed

The Top 100 Percent is also harmfull as per this article..TOP 100 Percent ORDER BY Considered Harmful.

*Emphasis Mine

Answer 2

ORDER BY fulfils two completely different roles - it can define the sort order of a result set, or it can complete the definition of other functionality, such as TOP or FETCH. Sometimes it may fulfil both of these roles at the same time.

This is an alternative formulation of your query:

SELECT * FROM (SELECT Top 2 'Set1', Order_No, format(Order_Date,'g'), First_Name
FROM        db1.dbo.Orders ORDER BY <Set 1 TOP Definition Criteria) t (Setname,Order_No,Order_Date,First_Name)
Union all
SELECT * FROM (SELECT Top 2 'Set2', Order_No, format(Order_Date,'g'), First_Name
FROM        db2.dbo.Orders ORDER BY <Set 2 TOP Definition Criteria) t (Setname,Order_No,Order_Date,First_Name)
Union all
SELECT * FROM (SELECT Top 2 'Set3', Order_No, format(Order_Date,'g'), First_Name 
FROM        db3.dbo.Orders ORDER BY <Set 3 TOP Definition Criteria) t (Setname,Order_No,Order_Date,First_Name)

ORDER BY    <Result set sorting criteria>

Here you can explicitly indicate by which criteria each TOP 2 should be selected. If you're using this within a view, however, you would omit the final ORDER BY <Result set sorting criteria> since views cannot dictate a result set sorting criteria. At best, you might force the server to accept the existence of such an ORDER BY clause, but it will offer no actual guarantees.

It's unclear from your question what the actual criteria ought to be for each TOP definition.