Reuse a complex non-deterministic expression in SELECT and WHERE clauses I have a query like this:

-- This is inefficient, and may even be wrong if expression is nondeterministic
SELECT TOP 10 team_name,score_points=&lt;big expression&gt;
  FROM teams
 WHERE &lt;big expression&gt; &gt;= 100
 ORDER BY 2 DESC

-- This gives error
SELECT TOP 10 team_name,score_points=&lt;big expression&gt;
  FROM teams
 WHERE score_points &gt;= 100
 ORDER BY 2 DESC


What I want to do is clear: list the teams with the highest score_points. List all teams with at least 100 score_points, but no more than 10 teams.

If I repeat the expression, it will be very ugly and inefficient. Plus, it will give wrong results when my expression is not deterministic.
Using the name score_points in the WHERE clause gives an error.
Is there an efficient way to have the results of my expression both returned as a column in my query, and used as a filter?

Please don't tell me about computed columns, I know about them but I can't use them here.
This can't help with the determinism issue, but CROSS APPLY is a good tool for big expressions that need to be reused:

SELECT TOP 10 team_name, CxA.Score_Points
FROM teams t
CROSS APPLY (SELECT Score_Points = &lt;big Expression, refer to t&gt;) CxA
 WHERE CxA.Score_Points &gt;= 100
 ORDER BY CxA.Score_Points DESC


CROSS APPLY expressions get calculated for every row and can be referred to in any part of the query.