title: Using SQL column Alias in built-in function?
body: <p>I'm trying to order a selection based on the alias but I can't figure out how. Here is an example:</p>
  
  <pre><code>select distinct top 100 id, 
                      col1, 
                      col2, 
                      CASE WHEN @orderFormat = 'this' then col1
                           WHEN @orderFormat = 'that' then col2
                      END as orderby
  from table
  where col1 = like '%'
  order by Len(orderby) asc, orderby asc
  </code></pre>
  
  <p>Whenever I pass my alias 'orderby' as an argument, it is reported as an invalid column.</p>
  
  <p>My goal is to be able to order a variable column alphanumerically. I know that 'order by Len(orderby) asc, orderby asc works, but just not with an alias.</p>
  
  <p>Anybody know a good way around this or if I'm doing something wrong?</p>
  
  <p>Thanks!</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I've managed to strip the select function down to this:</p>
  
  <pre><code>select top 200 Clip_Name as orderby
                 from Clips
  order by       Len(orderby) asc, orderby asc
  </code></pre>
  
  <p>Clip_Name is declared as <code>column Clip_Name(nvarchar, not null)</code>. The error from Microsoft SQL Server 2008 R2 Edition is <code>Msg 207, Level 16, State 1, Line 1
  Invalid column name 'orderby'</code>.</p>
  
  <p>However, this works (without the alias):</p>
  
  <pre><code>select top 200 Clip_Name 
                 from Clips 
  order by len(FLE_ID) desc, FLE_ID desc
  </code></pre>