T-SQL Column alias on computed column - Invalid column name

I'm using an alias to refer to a computed column. Here is a snippet from the actual code I'm trying to make work, to compute similarity and return matches where the similarity score is 3 or higher.

select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]
where similarity > 2
order by similarity desc

Excepti开发者_开发百科on Message:

Invalid column name 'similarity'.

As similarity is not a real column, how would I make this work?

Column aliases and computations are performed in the projection (SELECT) phase of the query, which occurs after the selection (WHERE and JOIN) phase. Because of this, they can't be referenced in the WHERE clause or in a JOIN condition because they do not yet exist. You can either use your query with the SELECT clause as a subquery or you can duplicate the computation in the WHERE clause:

select * 

from
(select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]) src

where similarity > 2
order by similarity desc

or

select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]
where difference([FirstName], 'mitch') > 2
order by similarity desc

All answers can solve your problem but for complicated situation you just can't duplicate your query.

The correct way is by using CROSS and APPLY

select [FirstName], similarity
from [Dev].[dbo].[Name]
cross apply
  (
     select similarity =
     difference([FirstName], 'mitch')  
  )computed_column
where similarity > 2
order by similarity desc

whit CROSS and APPLY you can use your computed column everywhere on the query

Try:

SELECT * 
  FROM (
        SELECT [FirstName], difference([FirstName], 'mitch') as similarity
            FROM [Dev].[dbo].[Name]
        ) a
WHERE similarity > 2
ORDER BY similarity desc

select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]
where difference([FirstName], 'mitch') > 2
order by 2 desc

You can't reference column aliases in where clause