Is it possible to use the result of a subquery in a case statement of the same outer query?

Question

I am writing a search routine with a ranking algorithm and would like to get this in one pass.

My Ideal query would be something like this....

select *, (select top 1 wordposition
           from wordpositions
           where recordid=items.pk_itemid and wordid=79588 and nextwordid=64502
          ) as WordPos,
      case when WordPos<11 then 1 else case WordPos<50 then 2 else case WordPos<100 then 3 else 4 end end end end as rank
from items

Is it possible to use WordPos in a case right there? It's generating an error on me , Invalid column name 'WordPos'.

I know I can redo the subquery for each case but I think it would actually re-run the case wouldn't it?

For example:

select *, case when (select top 1 wordposition from wordpositions where recordid=items.pk_itemid and wordid=79588 and nextwordid=64502)<11 then 1 else case (select top 1 wordposition from wordpositions where recordid=items.pk_itemid and wordid=79588 and nextwordid=64502)<50 then 2 else case (select top 1 wordposition from wordpositions where recordid=items.pk_itemid and wordid=79588 and nextwordid=64502)<100 then 3 else 4 end end end end as rank from items

That works....but is it really re-running the identical query each time?

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?

Just curious what the best way to do this would be... Thank you! Ryan

Yes sorry T-SQL not MySQL...fixed the tag... – Ryan Jones Nov 06 '13 at 21:47 — Ryan Jones, Nov 06 '13 at 21:47

score 1 · Accepted Answer · answered Nov 01 '13 at 22:40

You can do this using a subquery. I will stick with your SQL Server syntax, even though the question is tagged mysql:

select i.*,
       (case when WordPos < 11 then 1
             when WordPos < 50 then 2
             when WordPos < 100 then 3
        else 4
        end) as rank
from (select i.*,
             (select top 1 wpwordposition
              from wordpositions wp
              where recordid=i.pk_itemid and wordid=79588 and nextwordid=64502
             ) as WordPos
      from items i
     ) i;

This also simplifies the case statement. You do not need nested case statements to handle multiple conditions, just multiple where clauses.

That worked thanks! Both answers are right...I choose this one because the example was directly usable in my project. Also thanks for the tip on simplifying the case! — Ryan Jones, Nov 06 '13 at 21:48

score 0 · Answer 2 · edited May 23 '17 at 12:13

No. Identifiers introduced in the output clause (the fact that it comes from a sub-query is irrelevant) cannot be used within the same SELECT statement.

Here are some solutions:

Rewrite the query using a JOIN¹, This will eliminate the issue entirely and fits well with RA.
Wrap the entire SELECT with the sub-query within another SELECT with the case. The outer select can access identifiers introduced by the inner SELECT's output clause.
Use a CTE (if SQL Server). This is similar to #2 in that it allows an identifier to be introduced.

While "re-writing" the sub-query for each case is very messy it should still result in an equivalent plan - but view the query profile! - as the results of the query are non-volatile. As such the equivalent sub-queries can be safely moved by the query planner which should move the sub-query/sub-queries to a JOIN to avoid any "re-running" in the first place.

¹ Here is a conversion to use a JOIN, which is my preferred method. (I find that if a query can't be written in terms of a JOIN "easily" then it might be asking for the wrong thing or otherwise be showing issues with schema design.)

select
   wp.wordposition as WordPos,
   case wp.wordposition .. as Rank
from items i
left join wordpositions wp
  on wp.recordid = i.pk_itemid
where wp.wordid = 79588
  and wp.nextwordid = 64502

I've made assumptions about the multiplicity here (i.e. that wordid is unique) which should be verified. If this multiplicity is not valid and not correctable otherwise (and you're indeed using SQL Server), then I'd recommend using ROW_NUMBER() and a CTE.

Is it possible to use the result of a subquery in a case statement of the same outer query?

2 Answers2