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

Question

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

Exception Message:

Invalid column name 'similarity'.

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

Answer 1

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