title: SQL Server: ORDER BY in subquery with UNION
body: <p>i have two queries being combined with a <code>UNION ALL</code><sup>1</sup>:</p>
  
  <pre><code>--Query 1
  SELECT Flavor, Color
  FROM Friends
  </code></pre>
  
  <p>&nbsp;</p>
  
  <pre><code>--Query 2
  SELECT Flavor,
      (SELECT TOP 1 Color
       FROM Rainbows
       WHERE Rainbows.StrangerID = Strangers.StrangerID
       ORDER BY Wavelength DESC
      ) AS Color
  FROM Strangers
  </code></pre>
  
  <p>Both of which, of course, work fine separately, but when combined with a <code>UNION ALL</code>:</p>
  
  <pre><code>SELECT Flavor, Color
  FROM Friends
  
  UNION ALL
  
  SELECT Flavor,
      (SELECT TOP 1 Color
       FROM Rainbows
       WHERE Rainbows.StrangerID = Strangers.StrangerID
       ORDER BY Wavelength DESC
      ) AS Color
  FROM Strangers
  </code></pre>
  
  <p>The query fails with the error:</p>
  
  <blockquote>
    <p>Msg 104, Level 15, State 1, Line 3<br>
       ORDER BY items must appear in the select list if the statement contains a UNION operator.</p>
  </blockquote>
  
  <p>How do i use an ORDER BY in a statement with a UNION ALL?</p>
  
  <h2>Copy-Pasteable Example</h2>
  
  <pre><code>CREATE TABLE Friends (Flavor int, Color int)
  CREATE TABLE Strangers (Flavor int, StrangerID int)
  CREATE TABLE Rainbows (StrangerID int, Color int, Wavelength int)
  go
  
  SELECT Flavor, Color
  FROM Friends
  
  UNION ALL
  
  SELECT Flavor,
      (SELECT TOP 1 Color
       FROM Rainbows
       WHERE Rainbows.StrangerID = Strangers.StrangerID
       ORDER BY Wavelength DESC
      ) AS Color
  FROM Strangers
  go
  
  DROP TABLE Rainbows
  DROP TABLE Strangers
  DROP TABLE Friends
  </code></pre>
  
  <blockquote>
    <p>Server: Msg 104, Level 15, State 1, Line 2<br>
    ORDER BY items must appear in the select list if the statement contains a UNION operator.</p>
  </blockquote>
  
  <h2>Footnotes</h2>
  
  <ul>
  <li><sup>1</sup>Contrived hypothetical example. Or not.</li>
  </ul>
  
  <h2>See also</h2>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/213851/sql-query-using-order-by-in-union">SQL Query - Using Order By in UNION</a></li>
  <li><a href="https://stackoverflow.com/questions/487515/sql-union-and-order-by">SQL UNION and ORDER BY</a></li>
  <li><a href="https://stackoverflow.com/questions/842798/sql-using-top-1-in-union-query-with-order-by">SQL: Using Top 1 in UNION query with Order By</a></li>
  <li><a href="https://stackoverflow.com/questions/2548854/impact-of-ordering-of-correlated-subqueries-within-a-projection">Impact of ordering of correlated subqueries within a projection</a></li>
  </ul>