title: SQL: Using Top 1 in UNION query with Order By
body: <p>I have a table as below</p>
  
  <pre><code>Rate Effective_Date
  ---- --------------
  5.6  02/02/2009
  5.8  05/01/2009
  5.4  06/01/2009
  5.8  12/01/2009
  6.0  03/15/2009
  </code></pre>
  
  <p>I am supposed to find the all rates that are effective for current date and after it. So to get the current effective rate, i use</p>
  
  <pre><code>SELECT TOP 1 * from table 
  where effective_date &lt; '05/05/2009' 
  order by effective date desc
  </code></pre>
  
  <p>for the rates after the current date the query is </p>
  
  <pre><code>SELECT * from table 
  where effective_date &gt; '05/05/2009'
  </code></pre>
  
  <p>To combine these two result i use a union as</p>
  
  <pre><code>SELECT TOP 1 * from table 
  where effective_date &lt; '05/05/2009' 
  order by effective date desc
  
  UNION
  
  SELECT * from table 
  where effective_date &gt; '05/05/2009'
  </code></pre>
  
  <p>The expected result is </p>
  
  <pre><code>Rate Effective Date
  ---- --------------
  5.8  05/01/2009
  5.4  06/01/2009
  5.8  12/01/2009
  6.0  03/15/2009
  </code></pre>
  
  <p>But I get the actual result as</p>
  
  <pre><code>Rate Effective Date
  ---- --------------
  5.6  02/02/2009
  5.4  06/01/2009
  5.8  12/01/2009
  6.0  03/15/2009
  </code></pre>
  
  <p>I don't have a clue as to why this happens? Any suggestions?</p>