title: Is it possible to use the result of a subquery in a case statement of the same outer query?
body: <p>I am writing a search routine with a ranking algorithm and would like to get this in one pass.</p>
  
  <p>My Ideal query would be something like this....</p>
  
  <pre><code>select *, (select top 1 wordposition
             from wordpositions
             where recordid=items.pk_itemid and wordid=79588 and nextwordid=64502
            ) as WordPos,
        case when WordPos&lt;11 then 1 else case WordPos&lt;50 then 2 else case WordPos&lt;100 then 3 else 4 end end end end as rank
  from items 
  </code></pre>
  
  <p>Is it possible to use WordPos in a case right there? It's generating an error on me , Invalid column name 'WordPos'.</p>
  
  <p>I know I can redo the subquery for each case but I think it would actually re-run the case wouldn't it? </p>
  
  <p>For example:</p>
  
  <pre><code>select *, case when (select top 1 wordposition from wordpositions where recordid=items.pk_itemid and wordid=79588 and nextwordid=64502)&lt;11 then 1 else case (select top 1 wordposition from wordpositions where recordid=items.pk_itemid and wordid=79588 and nextwordid=64502)&lt;50 then 2 else case (select top 1 wordposition from wordpositions where recordid=items.pk_itemid and wordid=79588 and nextwordid=64502)&lt;100 then 3 else 4 end end end end as rank from items 
  </code></pre>
  
  <p>That works....but is it really re-running the identical query each time?</p>
  
  <p>It's hard to tell from the tests as the first time it runs it's slow but subsequent runs are quick....it's caching...so would that mean that the first time it ran it for the first row, the subsequent three times it would get the result from cache? </p>
  
  <p>Just curious what the best way to do this would be...
  Thank you!
  Ryan</p>