title: T-SQL Union (distinct, not all) and Order by with priority
body: <p>Assume I have a <code>Tag</code> table (<code>Id int</code> and <code>Name nvarchar(100)</code>) with 3 values:</p>
  
  <pre><code>1 Software
  2 Hardware
  3 Warehouse
  </code></pre>
  
  <p>Now I want to query with keyword, and prioritize the ones that starts with the keyword over the containing one. So at first I write this query:</p>
  
  <pre><code>SELECT 0 AS SortCol, * 
  FROM Tag T
  WHERE CHARINDEX(@keyword, T.Name) = 1
  UNION
  SELECT 1 AS SortCol, * 
  FROM Tag T
  WHERE T.Name LIKE ('%' + @keyword + '%')
  ORDER BY SortCol, Name;
  </code></pre>
  
  <p>However that did not work because the <code>SortCol</code> column no longer make them distinct (the <code>Warehouse</code> value appears twice because it is correct in both).</p>
  
  <p><a href="https://i.stack.imgur.com/Kvhc5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Kvhc5.png" alt="enter image description here"></a></p>
  
  <p>That's when I think I need to manually call <code>DISTINCT</code> by getting rid of that column:</p>
  
  <pre><code>SELECT DISTINCT T2.Id, T2.Name
  FROM
      (SELECT 0 AS SortCol, * 
       FROM Tag T
       WHERE CHARINDEX(@keyword, T.Name) = 1
       UNION
       SELECT 1 AS SortCol, * 
       FROM Tag T
       WHERE T.Name LIKE ('%' + @keyword + '%')
       ORDER BY SortCol, T.Name) AS T2;
  </code></pre>
  
  <p>However, this does not work because I get this error:</p>
  
  <blockquote>
    <p>The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.</p>
  </blockquote>
  
  <p>What am I missing here? How can I use <code>UNION</code> with <code>ORDER BY</code> in both statements?</p>