title: Should I use Top(1) in a SubQuery
body: <p>Example Query:</p>
  
  <pre><code>select * 
  from A join B on A.ID = B.SOMEVALUE
  where A.VALUE="something" and
  B.ID = 
         (select ID from B where SOMEVALUE = A.ID and 
                THISDATE = (select max(SOMEDATE) from B where ...))
  </code></pre>
  
  <p>so, if you can read SQL you should see that I am doing a couple correlated subqueries to narrow down the results of the join . (and yes, this is horribly over-simplified).</p>
  
  <p>In certain cases the subquery:</p>
  
  <pre><code>select ID from B where SOMEVALUE = A.ID and 
      THISDATE = (select max(SOMEDATE) from B where ...)
  </code></pre>
  
  <p>can return more than 1 value, which causes an error </p>
  
  <blockquote>
    <p>"Subquery returned more than 1 value.
    This is not permitted when the
    subquery follows =, !=, &lt;, &lt;= , >, >=
    or when the subquery is used as an
    expression."</p>
  </blockquote>
  
  <p>which I fully expect.  This is obviously not a good thing and I have code in place to (hopefully) prevent these duplicates from getting into the database in the first place (ie table B <em>should</em> only have 1 row that matches the </p>
  
  <pre><code>SOMEVALUE = A.ID and max(SOMEDATE)
  </code></pre>
  
  <p>criteria), however end-users are nothing if not creative in finding ways I can't think of to break software.</p>
  
  <p>So now to my question:</p>
  
  <p>Would it be better to change the first subquery to </p>
  
  <pre><code>select top 1 * from B ...
  </code></pre>
  
  <p>to prevent the user from seeing an error when/if (hopefully never) this situation arises or let the error come through.  I'm leaning to not adding the top statement and letting the error come through rather then let the user see potentially incorrect data.  I'm wondering if anyone has any thoughts on Best Practices in a situation like this...</p>