title: Wrapping SQL query into outer Select causes Order By reshuffle
body: <p>I have an engine that builds a query. So this is not static and this is why I had to go this way (below). Plus, it works for SQL and Oracle (Oracle adds different wrapper, RowNum, etc...). I have no easy way to test Oracle but below is SQL Server problem, step-by-step logic</p>
  
  <p>Lets take a simple query</p>
  
  <pre><code>Select field1 as f1, myDate dateFld From table1 t1 Where t1.field2 = 1
  </code></pre>
  
  <p>I may or may not,  have to <code>union</code> output with another table</p>
  
  <pre><code>Select field1, myDate dateFld as f1 From table1 t1 Where t1.field2 = 1
  Union
  Select field2, myDate dateFld as f1 From table2 t2 Where t2.field2 = 2
  </code></pre>
  
  <p>I need to get only <strong>N</strong> records from this Union</p>
  
  <pre><code>Select Top(N) * 
  From
     ( 
      Select field1 as f1, myDate dateFld From table1 t1 Where t1.field2 = 1
      Union
      Select field2 as f1, myDate dateFld From table2 t2 Where t2.field2 = 2
     ) Union_Tbl_Alias
  Order By dateFld Desc, f1 
  </code></pre>
  
  <p>Remember this "Order by"</p>
  
  <p>I also have Select Subqueries (and nothing I can do but have them in Select), which I moved to yet another Select wrapper</p>
  
  <pre><code>Select 
      f1, 
      myDate,
      (Select field99 From table99 t99 Where t99.f1 = Outer_Tbl_Alias.f1) as f3
  From
      (
          Select Top(N) * 
          From
             ( 
              Select field1 as f1, myDate dateFld From table1 t1 Where t1.field2 = 1
              Union
              Select field2 as f1, myDate dateFld From table2 t2 Where t2.field2 = 2
             )
          Order By dateFld Desc, f1
      ) Outer_Tbl_Alias
  </code></pre>
  
  <p>So the problem is that outer-most select reshuffles records a bit. They no longer sorted dateFld Desc.
  I don't want to speculate, I think, this is only SQL Server issue but I will test it in oracle as well. Moving "Order By" to outer-most statement fixes it for SQL Server. </p>
  
  <p>But I'm wondering:</p>
  
  <p>1 - <strong>why it happens?</strong></p>
  
  <p>2 - <strong>is there a hint to tell SQL server - keep the order of inner Select?</strong></p>