Help with RANK() Over Scalar Function - SQL Server 2008

I have the following Inline Tabl开发者_如何学JAVAe-Valued Function:

SELECT   Locations.LocationId, 
         dbo.Search_GetSuitability(@SearchPreferences,
            Score.FieldA, Score.FieldB, Score.FieldC) AS OverallSuitabilityScore,
        RANK() OVER (ORDER BY OverallSuitabilityScore) AS OverallSuitabilityRank

FROM        dbo.LocationsView Locations
INNER JOIN  dbo.LocationScores Score ON Locations.LocationId = Score.LocationId
WHERE       Locations.CityId = @LocationId   

That RANK() line is giving me an error:

Invalid column name 'OverallSuitabilityScore'.

The function dbo.Search_GetSuitability is a scalar-function which returns a DECIMAL(8,5). I need to assign a rank to each row based on that value.

The only way i can get the above to work is to add the scalar function call in the ORDER BY part again - which is silly. I have about 5 of these scalar function calls and i need seperate RANK() values for each.

What can i do? Can i use a Common Table Expression (CTE) ?

Yep, you can't reference a column alias in the SELECT clause. The CTE sounds good though. Here's an example

WITH Score as
    (
    select Score.LocationId, Score.FieldA, Score.FieldB, Score.FieldC,
        dbo.Search_GetSuitability(@SearchPreferences,
            Score.FieldA, Score.FieldB, Score.FieldC) AS OverallSuitabilityScore
    from dbo.LocationScores
    )

SELECT   TOP(10) 
         Locations.LocationId, 
         Score.OverallSuitabilityScore,
        RANK() OVER (ORDER BY OverallSuitabilityScore) AS OverallSuitabilityRank

FROM        dbo.LocationsView Locations
INNER JOIN  Score ON Locations.LocationId = Score.LocationId
WHERE       Locations.CityId = @LocationId   

An old school way of doing this is just to SUBQUERY the expression. The CTE here only moves the subquery to the top

SELECT   TOP(10) LocationId, 
        OverallSuitabilityScore,
        RANK() OVER (ORDER BY OverallSuitabilityScore) AS OverallSuitabilityRank
FROM
(
    SELECT
         Locations.LocationId, 
         dbo.Search_GetSuitability(@SearchPreferences,
             Score.FieldA, Score.FieldB, Score.FieldC) AS OverallSuitabilityScore
    FROM        dbo.LocationsView Locations
    INNER JOIN  dbo.LocationScores Score ON Locations.LocationId = Score.LocationId
    WHERE       Locations.CityId = @LocationId   
) X