title: "Invalid Column Name" error in query with order by case statement and outer select
body: <p>I am trying to order by a column in an outer select statement using a case statement to determine the fields to order by. For some reason, I cannot use the column created in the outer select. If I try to order by that column without using a case statement then it works. What am I doing wrong?</p>
  
  <p>Pseudocode to illustrate problem:</p>
  
  <pre><code>DECLARE @orderBy varchar(8)
  SET @orderBy = 'rating'
  
  SELECT
      CASE
          WHEN rating is not null THEN 1
          ELSE  0
      END as hasRating,
      *
  FROM
  
  (SELECT col1, ...) AS table1
  
  LEFT OUTER JOIN
  
  (SELECT col1, rating, ...) AS table2
  
  ON table1.col1 = table2.col2
  
  --causes error "Invalid column name 'hasRating'"
  ORDER BY 
      CASE WHEN @orderBy = 'rating' THEN hasRating END DESC,
      CASE WHEN @orderby = 'something else' THEN ...
  
  --works
  ORDER BY hasRating desc
  </code></pre>